<?php
namespace App\Models;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;
use App\Models\Datawhole;
use App\Models\ResourceModel\GoodTransfersDetailModel;
class InventoryModel1 extends BaseModel
{
    protected $goodTransfersDetailModel;
    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
        $this->goodTransfersDetailModel = new GoodTransfersDetailModel();
    }

    public function getInventory($params){
        $posttype = $params['posttype']??1;
        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;
        $sku = isset($params['sku']) ? $params['sku'] : null;
        $user_id = isset($params['user_id']) ? $params['user_id'] : 0;
        $shop_id = isset($params['shop_id']) ? $params['shop_id'] : 0;
        //初始化查询
        $thisDb = Db::table('self_sku as a')
            ->leftJoin('self_spu as b','a.spu','=','b.spu')
            ->leftJoin('self_custom_sku as c','a.custom_sku','=','c.custom_sku')
            ->leftJoin('self_color_size as d','c.size','=','d.name');
        //分页
        $pagenNum=$page-1;
        if ($pagenNum != 0) {
            $pagenNum = $limit * $pagenNum;
            $limit=$limit*$page;
        }
        if(!empty($sku)){
            $thisDb = $thisDb->where('a.sku','like','%'.$sku.'%')->orWhere('a.old_sku','like','%'.$sku.'%');
        }
        if($user_id!=0){
            $thisDb = $thisDb->where('a.user_id','=',$user_id);
        }
        if($shop_id!=0){
            $thisDb = $thisDb->where('a.shop_id','=',$shop_id);
        }
        if(isset($params['one_cate_id'])){
            $thisDb = $thisDb->whereIn('b.one_cate_id',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])){
            $thisDb = $thisDb->whereIn('b.two_cate_id',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])){
            $thisDb = $thisDb->whereIn('b.three_cate_id',$params['three_cate_id']);
        }
        $count=$thisDb->count();//数据总数

        if($posttype==1){
            $thisDb = $thisDb
                ->offset($pagenNum)
                ->limit($limit);
        }
        //执行查询
        $data = $thisDb
            ->select('a.sku','a.old_sku','c.img','a.user_id','a.shop_id','b.one_cate_id','b.two_cate_id','b.three_cate_id','c.size','d.sort')
//            ->orderby('a.shop_id','ASC')
//            ->orderby('a.user_id','ASC')
            ->orderby('b.spu','ASC')
            ->orderby('c.color','ASC')
            ->orderby('d.sort','ASC')
            ->orderby('a.id','DESC')
            ->get()
            ->toArray();
        //新旧sku组合成数组
        $skulist = array_column($data,'sku');
        $oldskulist = array_column($data,'old_sku');
        $skuArr=array_merge($skulist,$oldskulist);
        //查询库存
        $product_detail = Db::table('product_detail')->whereIn('sellersku',$skuArr)->select('sellersku','in_stock_num','un_stock_num','in_bound_num','transfer_num')->get()->toArray();
        //查询库龄
        $inventory_aged = Db::table('amazon_inventory_aged')->whereIn('sku',$skuArr)->select('sku','day_0_90','day_91_180','day_181_270','day_271_365','day_365')->get()->toArray();
        $inven = [];//库存数组  sku=>三种库存
        foreach ($product_detail as $detail){
            $inven[$detail->sellersku] = ['in_stock_num'=>$detail->in_stock_num,'in_bound_num'=>$detail->in_bound_num,'un_stock_num'=>$detail->un_stock_num,'transfer_num'=>$detail->transfer_num];
        }
        $inven_age = [];//库龄数组  sku=>五种库龄数值

        foreach ($inventory_aged as $aged){
            $inven_age[$aged->sku] = ['day_0_90'=>$aged->day_0_90,'day_91_180'=>$aged->day_91_180,'day_181_270'=>$aged->day_181_270,'day_271_365'=>$aged->day_271_365,'day_365'=>$aged->day_365];
        }

        foreach ($data as $key =>$val){
            //运营
            $val->account = '';
            $account = $this->GetUsers($val->user_id);
            if($account){
                $val->account = $account['account'];
            }
            //店铺
            $val->shop_name = '';
            $shop_name = $this->GetShop($val->shop_id);
            if($shop_name){
                $val->shop_name = $shop_name['shop_name'];
            }
            //大类
            $val->one_cate = '';
            $one_cate = $this->GetCategory($val->one_cate_id);
            if($one_cate){
                $val->one_cate = $one_cate['name'];
            }
            //二类
            $val->two_cate = '';
            $two_cate = $this->GetCategory($val->two_cate_id);
            if($two_cate){
                $val->two_cate = $two_cate['name'];
            }
            //三类
            $val->three_cate = '';
            $three_cate = $this->GetCategory($val->three_cate_id);
            if($three_cate){
                $val->three_cate = $three_cate['name'];
            }
            //库存赋值
            if(array_key_exists($val->sku,$inven)){
                $data[$key]->in_stock_num = $inven[$val->sku]['in_stock_num'];
                $data[$key]->un_stock_num = $inven[$val->sku]['un_stock_num'];
                $data[$key]->in_bound_num = $inven[$val->sku]['in_bound_num'];
                $data[$key]->transfer_num = $inven[$val->sku]['transfer_num'];
            }elseif(array_key_exists($val->old_sku,$inven)){
                $data[$key]->in_stock_num = $inven[$val->old_sku]['in_stock_num'];
                $data[$key]->un_stock_num = $inven[$val->old_sku]['un_stock_num'];
                $data[$key]->in_bound_num = $inven[$val->old_sku]['in_bound_num'];
                $data[$key]->transfer_num = $inven[$val->old_sku]['transfer_num'];
            }else{
                $data[$key]->in_stock_num = 0;
                $data[$key]->in_bound_num = 0;
                $data[$key]->transfer_num = 0;
            }
            //库龄赋值
            if(array_key_exists($val->sku,$inven_age)){
                $data[$key]->day_0_90 = $inven_age[$val->sku]['day_0_90'];
                $data[$key]->day_91_180 = $inven_age[$val->sku]['day_91_180'];
                $data[$key]->day_181_270 = $inven_age[$val->sku]['day_181_270'];
                $data[$key]->day_271_365 = $inven_age[$val->sku]['day_271_365'];
                $data[$key]->day_365 = $inven_age[$val->sku]['day_365'];
            }elseif(array_key_exists($val->old_sku,$inven_age)){
                $data[$key]->day_0_90 = $inven_age[$val->old_sku]['day_0_90'];
                $data[$key]->day_91_180 = $inven_age[$val->old_sku]['day_91_180'];
                $data[$key]->day_181_270 = $inven_age[$val->old_sku]['day_181_270'];
                $data[$key]->day_271_365 = $inven_age[$val->old_sku]['day_271_365'];
                $data[$key]->day_365 = $inven_age[$val->old_sku]['day_365'];
            }else{
                $data[$key]->day_0_90 = 0;
                $data[$key]->day_91_180 = 0;
                $data[$key]->day_181_270 = 0;
                $data[$key]->day_271_365 = 0;
                $data[$key]->day_365 = 0;
            }

            if($val->old_sku!=null&&$val->old_sku!=''){
                $val->sku = $val->old_sku;
            }

        }

        $posttype = $params['posttype']??1;
        if($posttype==2){
            if(count($data)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']='海外仓库存导出表'.time();

            $list = [
                'shop_name'=>'店铺名',
                'sku'=>'渠道sku',
                'old_sku'=>'渠道sku(旧)',
                'account'=>'运营',
                'in_bound_num'=>'在途',
                'un_stock_num'=>'不可售',
                'in_stock_num'=>'可售',
                'transfer_num'=>'预留',
                'day_0_90'=>'库龄<90',
                'day_91_180'=>'库龄90-180',
                'day_181_270'=>'库龄180-270',
                'day_271_365'=>'库龄270-365',
                'day_365'=>'库龄>365',
            ];

            // var_dump($user_ids);
            $p['title_list']  = $list;
            $p['data'] =  $data;
            $find_user_id = $params['find_user_id']??1;
            $p['user_id'] = $find_user_id;
            $p['type'] = '库存管理-海外仓导出';

            $job = new \App\Http\Controllers\Jobs\ExcelTaskController;
            try {
                $job::runJob($p);
            } catch(\Exception $e){
                return ['type' => 'fail', 'msg' => '加入队列失败'];
            }
            return ['type' => 'success','msg' => '加入队列成功'];
        }

        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }


    public function getSaiheInventory($params){
        $posttype = $params['posttype']??1;
        // $page = isset($params['page']) ? $params['page'] : 1;
        // $limit = isset($params['limit']) ? $params['limit'] : 30;
        $custom_sku = isset($params['custom_sku']) ? $params['custom_sku'] : null;
        $house_id = isset($params['house_id'])? $params['house_id'] : 1;
        //分页
        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }


        //初始化查询
        $thisDb = Db::table('self_custom_sku as a')->leftjoin('self_spu as b','a.spu','=','b.spu')->leftJoin('self_color_size as c','a.size','=','c.name');

        if($custom_sku!=null){
            $custom_sku =trim($custom_sku);
            $thisDb = $thisDb->where('a.custom_sku','like','%'.$custom_sku.'%')->orWhere('a.old_custom_sku','like','%'.$custom_sku.'%');
        }
        if(isset($params['one_cate_id'])){
            $thisDb = $thisDb->whereIn('b.one_cate_id',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])){
            $thisDb = $thisDb->whereIn('b.two_cate_id',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])){
            $thisDb = $thisDb->whereIn('b.three_cate_id',$params['three_cate_id']);
        }
        //数据总数
        $count=$thisDb->count();
        //执行查询
//        DB::connection()->enableQueryLog();
        if($posttype==1){
            $thisDb = $thisDb
                ->offset($page)
                ->limit($limit);
        }
        $data = $thisDb
            ->select('a.custom_sku','a.old_custom_sku','a.spu','b.one_cate_id','b.two_cate_id','b.three_cate_id','a.id','a.buy_price')
            ->orderby('a.spu','ASC')
            ->orderby('a.color','ASC')
            ->orderby('c.sort','ASC')
            ->orderby('a.id','DESC')
            ->get()
            ->toArray();
//        var_dump(DB::getQueryLog());
        //取出新旧库存sku组合成数组
        $custom_skuarr = array_column($data,'id');
//        $custom_skuarr = array_column($data,'custom_sku');
//        $old_custom_skuarr = array_column($data,'old_custom_sku');
//        $skuArr=array_merge($custom_skuarr,$old_custom_skuarr);
        //根据组合的库存sku数组查询赛盒同安fba仓，同安自发货仓，泉州仓库存
        if($house_id==1){
            $warehouse_id = [2,386];
        }else{
            $warehouse_id = [560];
        }
        $inventory = DB::table('saihe_inventory')->whereIn('custom_sku_id',$custom_skuarr)->whereIn('warehouse_id',$warehouse_id)->select('client_sku','good_num','warehouse_id','custom_sku_id')->get()->toArray();
        // var_dump( $inventory );
        $fbaArr = [];
        $selfArr = [];
        $quanzhouArr = [];
        //三个仓的库存分别组成数组
        foreach ($inventory as $invenVal){
            if($house_id==1){
                if($invenVal->warehouse_id==2){
                    if(isset($fbaArr[$invenVal->custom_sku_id])){
                        $fbaArr[$invenVal->custom_sku_id] += $invenVal->good_num;
                    }else{
                        $fbaArr[$invenVal->custom_sku_id] = $invenVal->good_num;
                    }

                }
                if($invenVal->warehouse_id==386){
                    if(isset($selfArr[$invenVal->custom_sku_id])){
                        $selfArr[$invenVal->custom_sku_id] += $invenVal->good_num;
                    }else{
                        $selfArr[$invenVal->custom_sku_id] = $invenVal->good_num;
                    }

                }
            }else{
                if($invenVal->warehouse_id==560){
                    if(isset($quanzhouArr[$invenVal->custom_sku_id])){
                        $quanzhouArr[$invenVal->custom_sku_id] += $invenVal->good_num;
                    }else{
                        $quanzhouArr[$invenVal->custom_sku_id] = $invenVal->good_num;
                    }

                }
            }
        }

        // var_dump( $quanzhouArr);
        //查询库存sku的补货计划
        $buhuo_request = Db::table('amazon_buhuo_request as a')
            ->join('amazon_buhuo_detail as b','a.id','=','b.request_id')
            ->whereIn('b.custom_sku_id',$custom_skuarr)
            ->whereIn('a.request_status',[3,4,5,6,7])
            ->where('warehouse',$house_id)
            ->select('b.request_num','b.custom_sku', 'b.transportation_mode_name', 'b.transportation_type')
            ->get()->toArray();
        $courierArr = [];
        $shippingArr = [];
        $airArr = [];
        $kahangArr = [];
        $railwayArr = [];
        // 五种快递方式分别组成数组
        foreach ($buhuo_request as $requestVal){
            switch ($requestVal->transportation_type){
                case 1:
                    // 海运
                    if(isset($shippingArr[$requestVal->custom_sku])){
                        $shippingArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $shippingArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 2:
                    // 快递
                    if(isset($courierArr[$requestVal->custom_sku])){
                        $courierArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $courierArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 3:
                    // 空运
                    if(isset($airArr[$requestVal->custom_sku])){
                        $airArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $airArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 4:
                    // 铁路
                    if(isset($railwayArr[$requestVal->custom_sku])){
                        $railwayArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $railwayArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 5:
                    // 卡航
                    if(isset($kahangArr[$requestVal->custom_sku])){
                        $kahangArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $kahangArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                default:
                    break;
            }
        }
        //赋值
        foreach ($data as $key=>$val){
            //大类
            $val->one_cate = '';
            $one_cate = $this->GetCategory($val->one_cate_id);
            if($one_cate){
                $val->one_cate = $one_cate['name'];
            }
            //二类
            $val->two_cate = '';
            $two_cate = $this->GetCategory($val->two_cate_id);
            if($two_cate){
                $val->two_cate = $two_cate['name'];
            }
            //三类
            $val->three_cate = '';
            $three_cate = $this->GetCategory($val->three_cate_id);
            if($three_cate){
                $val->three_cate = $three_cate['name'];
            }

            $data[$key]->inventory = 0;//库存

            $data[$key]->img = $this->GetCustomskuImg($val->custom_sku);//图片

            //库存赋值
            // if($house_id==1){
            //     if(array_key_exists($val->old_custom_sku,$fbaArr)){
            //         $fba_inventory = $fbaArr[$val->old_custom_sku];
            //     }elseif(array_key_exists($val->custom_sku,$fbaArr)){
            //         $fba_inventory = $fbaArr[$val->custom_sku];
            //     }else{
            //         $fba_inventory = 0;
            //     }
            //     if(array_key_exists($val->old_custom_sku,$selfArr)){
            //         $self_inventory = $selfArr[$val->old_custom_sku];
            //     }elseif(array_key_exists($val->custom_sku,$selfArr)){
            //         $self_inventory = $selfArr[$val->custom_sku];
            //     }else{
            //         $self_inventory = 0;
            //     }
            //     $data[$key]->inventory = $fba_inventory+$self_inventory;
            // }else{
            //     if(array_key_exists($val->old_custom_sku,$quanzhouArr)){
            //         $data[$key]->inventory = $quanzhouArr[$val->old_custom_sku];
            //     }elseif(array_key_exists($val->custom_sku,$quanzhouArr)){
            //         $data[$key]->inventory = $quanzhouArr[$val->custom_sku];
            //     }else{
            //         $data[$key]->inventory = 0;
            //     }
            // }
            if($house_id==1){
                if(array_key_exists($val->id,$fbaArr)){
                    $fba_inventory = $fbaArr[$val->id];
                }else{
                    $fba_inventory = 0;
                }
                if(array_key_exists($val->id,$selfArr)){
                    $self_inventory = $selfArr[$val->id];
                }else{
                    $self_inventory = 0;
                }
                $data[$key]->inventory = $fba_inventory+$self_inventory;
            }else{
                if(array_key_exists($val->id,$quanzhouArr)){
                    $data[$key]->inventory = $quanzhouArr[$val->id];
                }else{
                    $data[$key]->inventory = 0;
                }
            }


            //快递计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->custom_sku];
            }else{
                $data[$key]->courier_num = 0;
            }
            //海运计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->custom_sku];
            }else{
                $data[$key]->shipping_num = 0;
            }
            //空运计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$airArr)){
                $data[$key]->air_num = $airArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$airArr)){
                $data[$key]->air_num = $airArr[$val->custom_sku];
            }else{
                $data[$key]->air_num = 0;
            }
            // 铁路计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->custom_sku];
            }else{
                $data[$key]->railway_num = 0;
            }
            // 卡航计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->custom_sku];
            }else{
                $data[$key]->kahang_num = 0;
            }
            //计划发货数量合计
            $data[$key]->total_num = $data[$key]->courier_num+$data[$key]->shipping_num+$data[$key]->air_num+$data[$key]->railway_num+$data[$key]->kahang_num;
            $data[$key]->reduce_num = $data[$key]->inventory - $data[$key]->total_num;
            //库存周转天数
            $data[$key]->turnover =  $data[$key]->total_num>0 ? round( $data[$key]->inventory/$data[$key]->total_num,2) : 0;

            if($val->old_custom_sku!=null||$val->old_custom_sku!=''){
                $val->custom_sku = $val->old_custom_sku;
            }
        }



        if($posttype==2){
            if(count($data)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            if($house_id==1){
                $house = '同安';
            }else{
                $house = '泉州';
            }

            $p['title']=$house.'仓库存导出表'.time();

            $list = [
                'custom_sku'=>'库存sku',
                'old_custom_sku'=>'库存sku(旧)',
//                'img'=>'图片',
                'buy_price'=>'参考成本价',
                'one_cate'=>'大类',
                'three_cate'=>'三类',
                'inventory'=>$house.'仓库存',
                'shipping_num'=>'计划出货-海运',
                'air_num'=>'计划出货-空运',
                'courier_num'=>'计划出货-快递',
                'railway_num' => '计划出货-铁路',
                'kahang_num' => '计划出货-卡航',
                'total_num'=>'计划出货-合计',
                'turnover'=>'库存周转天数',
            ];

            // var_dump($user_ids);
            $p['title_list']  = $list;
            $p['data'] =  $data;
            $find_user_id = $params['find_user_id']??1;
            $p['user_id'] = $find_user_id;
            $p['type'] = '库存管理-'.$house.'仓导出';

            $job = new \App\Http\Controllers\Jobs\ExcelTaskController;
            try {
                $job::runJob($p);
            } catch(\Exception $e){
                return ['type' => 'fail', 'msg' => '加入队列失败'];
            }
            return ['type' => 'success','msg' => '加入队列成功'];
        }


        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }

    //同安仓-泉州仓出入库记录
    public function houseData($params){
        $client_sku = isset($params['client_sku'])? $params['client_sku'] : null;
        $start_time = isset($params['start_time'])? $params['start_time'] : date('Y-01-01 00:00:00');;
        $end_time = isset($params['end_time'])? $params['end_time'] : date('Y-m-d H:i:s');
        $house_id = isset($params['house_id'])? $params['house_id'] : 1;
        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;
        //分页
        $pagenNum=$page-1;
        if ($pagenNum != 0) {
            $pagenNum = $limit * $pagenNum;
            $limit=$limit*$page;
        }
        if(empty($client_sku)){
            return [
                'data' => '',
                'totalNum' => '',
            ];
        }
        //初始化查询
        $data = DB::table('saihe_warehouse_log')->where('client_sku',$client_sku);

        if(!empty($start_time)&&!empty($end_time)){
            $data = $data->whereBetween('operation_date', [$start_time, $end_time]);
        }
        //$house_id=>array();
        //2=>同安fba仓  386=>同安自发货仓  560=>泉州仓

        if($house_id==1){
            $data = $data->whereIn('warehouse_id',[2,386]);
        }else{
            $data = $data->whereIn('warehouse_id',[560]);
        }

        $count = $data->count();
        //执行查询
        $data = $data->offset($pagenNum)->limit($limit)->select('client_sku','operation_num','operation_date','warehouse_name','operation_username','remark','type')->get()->toArray();
        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }

    //云仓出入库记录
    public function cloudData($params){
        $client_sku = isset($params['client_sku'])? $params['client_sku'] : null;
        $start_time = isset($params['start_time'])? $params['start_time'] : date('Y-01-01 00:00:00');
        $end_time = isset($params['end_time'])? $params['end_time'] : date('Y-m-d H:i:s');
        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;
        //分页
        $pagenNum=$page-1;
        if ($pagenNum != 0) {
            $pagenNum = $limit * $pagenNum;
            $limit=$limit*$page;
        }
        if(empty($client_sku)){
            return [
                'data' => '',
                'totalNum' => '',
            ];
        }
        //初始化查询
        $data = DB::table('cloudhouse_record')->where('custom_sku',$client_sku);

        if(!empty($start_time)&&!empty($end_time)){
            $data = $data->whereBetween('createtime', [$start_time, $end_time]);
        }

        $count = $data->count();
        //执行查询
        $data = $data->offset($pagenNum)->limit($limit)->select('*')->get()->toArray();
        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }


    //云仓库存报表
    public function getCloudInventory($params){

        $posttype = $params['posttype']??1;

        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        $custom_sku = isset($params['custom_sku']) ? $params['custom_sku'] : null;
        //分页

        //初始化查询
        $thisDb = Db::table('self_custom_sku as a')
            ->leftjoin('self_spu as b','a.spu','=','b.spu')
            ->leftJoin('self_color_size as c','a.size','=','c.name');
        if(!empty($params['warehouse_id'])){
            if($params['warehouse_id']==3){
                $thisDb = $thisDb->where('a.is_cloud',1);
            }
        }
        if($custom_sku!=null){
            $custom_sku =trim($custom_sku);
            $thisDb = $thisDb->where('a.custom_sku','like','%'.$custom_sku.'%')->orWhere('a.old_custom_sku','like','%'.$custom_sku.'%');
        }
        if(isset($params['one_cate_id'])){
            $thisDb = $thisDb->whereIn('b.one_cate_id',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])){
            $thisDb = $thisDb->whereIn('b.two_cate_id',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])){
            $thisDb = $thisDb->whereIn('b.three_cate_id',$params['three_cate_id']);
        }
        //执行查询
        //    DB::connection()->enableQueryLog();
        if($posttype==1){
            $thisDb = $thisDb
                ->offset($page)
                ->limit($limit);
        }
        $data = $thisDb
            ->select('a.custom_sku','a.old_custom_sku','a.spu','b.one_cate_id','b.two_cate_id','b.three_cate_id','c.id as color_id','a.cloud_num','a.id','factory_num','tongan_inventory','quanzhou_inventory','deposit_inventory')
            ->where('a.cloud_num','!=',0)
            ->orderby('a.cloud_num','DESC')
            ->orderby('a.spu','ASC')
            ->orderby('a.color','ASC')
            ->orderby('c.sort','ASC')
            ->get()
            ->toArray();
        //数据总数
        $count=$thisDb->count();
        //    var_dump(DB::getQueryLog());
//        var_dump($data);
        //取出新旧库存sku组合成数组
        $custom_skuarr = array_column($data,'id');
//        $old_custom_skuarr = array_column($data,'old_custom_sku');
//        $skuArr=array_merge($custom_skuarr,$old_custom_skuarr);
        //查询库存sku的补货计划
        $buhuo_request = Db::table('amazon_buhuo_request as a')
            ->join('amazon_buhuo_detail as b','a.id','=','b.request_id')
            ->whereIn('b.custom_sku_id',$custom_skuarr)
            ->whereIn('a.request_status',[3,4,5,6,7])
            ->where('a.warehouse',$params['warehouse_id'])
            ->select('b.request_num','b.custom_sku', 'b.transportation_mode_name', 'b.transportation_type')
            ->get()->toArray();
        $courierArr = [];
        $shippingArr = [];
        $airArr = [];
        $kahangArr = [];
        $railwayArr = [];
        // 五种快递方式分别组成数组
        foreach ($buhuo_request as $requestVal){
            switch ($requestVal->transportation_type){
                case 1:
                    // 海运
                    if(isset($shippingArr[$requestVal->custom_sku])){
                        $shippingArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $shippingArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 2:
                    // 快递
                    if(isset($courierArr[$requestVal->custom_sku])){
                        $courierArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $courierArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 3:
                    // 空运
                    if(isset($airArr[$requestVal->custom_sku])){
                        $airArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $airArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 4:
                    // 铁路
                    if(isset($railwayArr[$requestVal->custom_sku])){
                        $railwayArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $railwayArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 5:
                    // 卡航
                    if(isset($kahangArr[$requestVal->custom_sku])){
                        $kahangArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $kahangArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                default:
                    break;
            }
        }

        //赋值
        foreach ($data as $key=>$val){
            //大类
            $val->one_cate = '';
            $one_cate = $this->GetCategory($val->one_cate_id);
            if($one_cate){
                $val->one_cate = $one_cate['name'];
            }
            //二类
            $val->two_cate = '';
            $two_cate = $this->GetCategory($val->two_cate_id);
            if($two_cate){
                $val->two_cate = $two_cate['name'];
            }
            //三类
            $val->three_cate = '';
            $three_cate = $this->GetCategory($val->three_cate_id);
            if($three_cate){
                $val->three_cate = $three_cate['name'];
            }

            //快递计划发货数量赋值
            if(array_key_exists($val->custom_sku,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->custom_sku];
            }elseif(array_key_exists($val->old_custom_sku,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->old_custom_sku];
            }else{
                $data[$key]->courier_num = 0;
            }
            //海运计划发货数量赋值
            if(array_key_exists($val->custom_sku,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->custom_sku];
            }elseif(array_key_exists($val->old_custom_sku,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->old_custom_sku];
            }else{
                $data[$key]->shipping_num = 0;
            }
            //空运计划发货数量赋值
            if(array_key_exists($val->custom_sku,$airArr)){
                $data[$key]->air_num = $airArr[$val->custom_sku];
            }elseif(array_key_exists($val->old_custom_sku,$airArr)){
                $data[$key]->air_num = $airArr[$val->old_custom_sku];
            }else{
                $data[$key]->air_num = 0;
            }
            // 铁路计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->custom_sku];
            }else{
                $data[$key]->railway_num = 0;
            }
            // 卡航计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->custom_sku];
            }else{
                $data[$key]->kahang_num = 0;
            }
            //计划发货数量合计
            $data[$key]->total_num = $data[$key]->courier_num+$data[$key]->shipping_num+$data[$key]->air_num+$data[$key]->railway_num+$data[$key]->kahang_num;
            $data[$key]->reduce_num = $val->cloud_num - $data[$key]->total_num;
            //同安库存周转天数
            $data[$key]->cloud_turnover =  $data[$key]->total_num>0 ? round($val->cloud_num/$data[$key]->total_num,2) : 0;

            $data[$key]->img = $this->GetCustomskuImg($val->custom_sku);//图片
            if($val->old_custom_sku!=null||$val->old_custom_sku!=''){
                $val->custom_sku = $val->old_custom_sku;
            }
        }


        if($posttype==2){

            $warehouse = DB::table('cloudhouse_warehouse')->where('id',$params['warehouse_id'])->select('warehouse_name')->first();
            if(!empty($warehouse)){
                $warehouse_title = $warehouse->warehouse_name;
            }else{
                $warehouse_title = '';
            }
            $p['title']=$warehouse_title.'库存导出表'.time();

            if(count($data)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }


            if($params['warehouse_id']==1){
                $list = [
                    'custom_sku'=>'库存sku',
                    'old_custom_sku'=>'库存sku(旧)',
//                'img'=>'图片',
                    'one_cate'=>'大类',
                    'three_cate'=>'三类',
                    'tongan_inventory'=>'同安仓库存',
                    'shipping_num'=>'计划出货-海运',
                    'air_num'=>'计划出货-空运',
                    'courier_num'=>'计划出货-快递',
                    'railway_num' => '计划出货-铁路',
                    'kahang_num' => '计划出货-卡航',
                    'total_num'=>'计划出货-合计',
                    'cloud_turnover'=>'同安仓库存周转天数',
                ];
            }
            if($params['warehouse_id']==2){
                $list = [
                    'custom_sku'=>'库存sku',
                    'old_custom_sku'=>'库存sku(旧)',
//                'img'=>'图片',
                    'one_cate'=>'大类',
                    'three_cate'=>'三类',
                    'quanzhou_inventory'=>'泉州仓库存',
                    'shipping_num'=>'计划出货-海运',
                    'air_num'=>'计划出货-空运',
                    'courier_num'=>'计划出货-快递',
                    'railway_num' => '计划出货-铁路',
                    'kahang_num' => '计划出货-卡航',
                    'total_num'=>'计划出货-合计',
                    'cloud_turnover'=>'泉州仓库存周转天数',
                ];
            }
            if($params['warehouse_id']==3){
                $list = [
                    'custom_sku'=>'库存sku',
                    'old_custom_sku'=>'库存sku(旧)',
//                'img'=>'图片',
                    'one_cate'=>'大类',
                    'three_cate'=>'三类',
                    'cloud_num'=>'云仓库存',
                    'shipping_num'=>'计划出货-海运',
                    'air_num'=>'计划出货-空运',
                    'courier_num'=>'计划出货-快递',
                    'railway_num' => '计划出货-铁路',
                    'kahang_num' => '计划出货-卡航',
                    'total_num'=>'计划出货-合计',
                    'cloud_turnover'=>'云仓库存周转天数',
                ];
            }
            if($params['warehouse_id']==4){
                $list = [
                    'custom_sku'=>'库存sku',
                    'old_custom_sku'=>'库存sku(旧)',
//                'img'=>'图片',
                    'one_cate'=>'大类',
                    'three_cate'=>'三类',
                    'factory_num'=>'工厂虚拟仓库存'
                ];
            }
            if($params['warehouse_id']==6){
                $list = [
                    'custom_sku'=>'库存sku',
                    'old_custom_sku'=>'库存sku(旧)',
//                'img'=>'图片',
                    'one_cate'=>'大类',
                    'three_cate'=>'三类',
                    'deposit_inventory'=>'工厂寄存仓库存',
                    'shipping_num'=>'计划出货-海运',
                    'air_num'=>'计划出货-空运',
                    'courier_num'=>'计划出货-快递',
                    'railway_num' => '计划出货-铁路',
                    'kahang_num' => '计划出货-卡航',
                    'total_num'=>'计划出货-合计',
                    'cloud_turnover'=>'工厂寄存仓库存周转天数',
                ];
            }


            // var_dump($user_ids);
            $p['title_list']  = $list;
            $p['data'] =  $data;
            $find_user_id = $params['find_user_id']??1;
            $p['user_id'] = $find_user_id;
            $p['type'] = '库存管理-'.$warehouse_title.'导出';

            $job = new \App\Http\Controllers\Jobs\ExcelTaskController;
            try {
                $job::runJob($p);
            } catch(\Exception $e){
                return ['type' => 'fail', 'msg' => '加入队列失败'];
            }
            return ['type' => 'success','msg' => '加入队列成功'];
        }
        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }

    public function factoryInventory($params){

        $posttype = $params['posttype']??1;

        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        $custom_sku = isset($params['custom_sku']) ? $params['custom_sku'] : null;
        //分页

        //初始化查询
        $thisDb = Db::table('self_custom_sku as a')
            ->leftjoin('self_spu as b','a.spu','=','b.spu')
            ->leftJoin('self_color_size as c','a.size','=','c.name');
        if($custom_sku!=null){
            $custom_sku =trim($custom_sku);
            $thisDb = $thisDb->where('a.custom_sku','like','%'.$custom_sku.'%')->orWhere('a.old_custom_sku','like','%'.$custom_sku.'%');
        }
        if(isset($params['one_cate_id'])){
            $thisDb = $thisDb->whereIn('b.one_cate_id',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])){
            $thisDb = $thisDb->whereIn('b.two_cate_id',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])){
            $thisDb = $thisDb->whereIn('b.three_cate_id',$params['three_cate_id']);
        }
        //数据总数
        $count=$thisDb->where('a.factory_num','!=',0)->count();

        if($posttype==1){
            $thisDb = $thisDb
                ->offset($page)
                ->limit($limit);
        }
        $data = $thisDb
            ->select('a.custom_sku','a.old_custom_sku','a.spu','b.one_cate_id','b.two_cate_id','b.three_cate_id','c.id as color_id','a.factory_num','a.id')
            ->where('a.factory_num','!=',0)
            ->orderby('a.factory_num','DESC')
            ->orderby('a.spu','ASC')
            ->orderby('a.color','ASC')
            ->orderby('c.sort','ASC')
            ->get()
            ->toArray();

        //取出新旧库存sku组合成数组
        $custom_skuarr = array_column($data,'id');
//        $old_custom_skuarr = array_column($data,'old_custom_sku');
//        $skuArr=array_merge($custom_skuarr,$old_custom_skuarr);
        //查询库存sku的补货计划
        $buhuo_request = Db::table('amazon_buhuo_request as a')
            ->join('amazon_buhuo_detail as b','a.id','=','b.request_id')
            ->whereIn('b.custom_sku_id',$custom_skuarr)
            ->whereIn('a.request_status',[3,4,5,6,7])
            ->where('a.warehouse',4)
            ->select('b.request_num','b.custom_sku', 'b.transportation_mode_name', 'b.transportation_type')
            ->get()->toArray();
        $courierArr = [];
        $shippingArr = [];
        $airArr = [];
        $kahangArr = [];
        $railwayArr = [];
        // 五种快递方式分别组成数组
        foreach ($buhuo_request as $requestVal){
            switch ($requestVal->transportation_type){
                case 1:
                    // 海运
                    if(isset($shippingArr[$requestVal->custom_sku])){
                        $shippingArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $shippingArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 2:
                    // 快递
                    if(isset($courierArr[$requestVal->custom_sku])){
                        $courierArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $courierArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 3:
                    // 空运
                    if(isset($airArr[$requestVal->custom_sku])){
                        $airArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $airArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 4:
                    // 铁路
                    if(isset($railwayArr[$requestVal->custom_sku])){
                        $railwayArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $railwayArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 5:
                    // 卡航
                    if(isset($kahangArr[$requestVal->custom_sku])){
                        $kahangArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $kahangArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                default:
                    break;
            }
        }

        //赋值
        foreach ($data as $key=>$val){
            //大类
            $val->one_cate = '';
            $one_cate = $this->GetCategory($val->one_cate_id);
            if($one_cate){
                $val->one_cate = $one_cate['name'];
            }
            //二类
            $val->two_cate = '';
            $two_cate = $this->GetCategory($val->two_cate_id);
            if($two_cate){
                $val->two_cate = $two_cate['name'];
            }
            //三类
            $val->three_cate = '';
            $three_cate = $this->GetCategory($val->three_cate_id);
            if($three_cate){
                $val->three_cate = $three_cate['name'];
            }

            //快递计划发货数量赋值
            if(array_key_exists($val->custom_sku,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->custom_sku];
            }elseif(array_key_exists($val->old_custom_sku,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->old_custom_sku];
            }else{
                $data[$key]->courier_num = 0;
            }
            //海运计划发货数量赋值
            if(array_key_exists($val->custom_sku,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->custom_sku];
            }elseif(array_key_exists($val->old_custom_sku,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->old_custom_sku];
            }else{
                $data[$key]->shipping_num = 0;
            }
            //空运计划发货数量赋值
            if(array_key_exists($val->custom_sku,$airArr)){
                $data[$key]->air_num = $airArr[$val->custom_sku];
            }elseif(array_key_exists($val->old_custom_sku,$airArr)){
                $data[$key]->air_num = $airArr[$val->old_custom_sku];
            }else{
                $data[$key]->air_num = 0;
            }
            // 铁路计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->custom_sku];
            }else{
                $data[$key]->railway_num = 0;
            }
            // 卡航计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->custom_sku];
            }else{
                $data[$key]->kahang_num = 0;
            }
            //计划发货数量合计
            $data[$key]->total_num = $data[$key]->courier_num+$data[$key]->shipping_num+$data[$key]->air_num+$data[$key]->railway_num+$data[$key]->kahang_num;
            $data[$key]->reduce_num = $val->factory_num - $data[$key]->total_num;
            //工厂库存周转天数
            $data[$key]->cloud_turnover =  $data[$key]->total_num>0 ? round($val->factory_num/$data[$key]->total_num,2) : 0;

            $data[$key]->img = $this->GetCustomskuImg($val->custom_sku);//图片
            if($val->old_custom_sku!=null||$val->old_custom_sku!=''){
                $val->custom_sku = $val->old_custom_sku;
            }
        }




        if($posttype==2){
            if(count($data)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']='工厂库存导出表'.time();

            $list = [
                'custom_sku'=>'库存sku',
                'old_custom_sku'=>'库存sku(旧)',
//                'img'=>'图片',
                'one_cate'=>'大类',
                'three_cate'=>'三类',
                'cloud_num'=>'工厂库存',
                'reduce_num'=>'预减后工厂库存',
                'shipping_num'=>'计划出货-海运',
                'air_num'=>'计划出货-空运',
                'courier_num'=>'计划出货-快递',
                'total_num'=>'计划出货-合计',
                'cloud_turnover'=>'工厂库存周转天数',
            ];

            // var_dump($user_ids);
            $p['title_list']  = $list;
            $p['data'] =  $data;
            $find_user_id = $params['find_user_id']??1;
            $p['user_id'] = $find_user_id;
            $p['type'] = '库存管理-工厂虚拟仓导出';

            $job = new \App\Http\Controllers\Jobs\ExcelTaskController;
            try {
                $job::runJob($p);
            } catch(\Exception $e){
                return ['type' => 'fail', 'msg' => '加入队列失败'];
            }
            return ['type' => 'success','msg' => '加入队列成功'];
        }
        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }

    public function goods_transfers($params){
        $token = $params['token'];

        if(empty($params['skuData'])){
            return ['code'=>500,'msg'=>'请选择要调动的商品'];
        }

        if(empty($params['in_house'])){
            return ['code'=>500,'msg'=>'请选择入仓仓库'];
        }

        if(empty($params['out_house'])){
            return ['code'=>500,'msg'=>'请选择出仓仓库'];
        }

        if(empty($params['yj_arrive_time'])){
            return ['code'=>500,'msg'=>'请填写预计到达时间'];
        }

        $user_data = DB::table('users')->where('token', '=', $token)->select('Id', 'account')->get();
        $user_id = $user_data[0]->Id;
        $createtime = date('Y-m-d H:i:s');

        if(is_array($params['skuData'])){
            $transfers['order_no'] = rand(100,999).time();
            $transfers['total_num'] = 0;
            $transfers['sku_count'] = count($params['skuData']);
            $transfers['out_house'] = $params['out_house'];
            $transfers['in_house'] = $params['in_house'];
            $transfers['type'] = 2;
            $transfers['yj_arrive_time'] = $params['yj_arrive_time'];
            $transfers['type_detail'] = $params['type_detail'];
            $transfers['address'] = isset($params['address'])? $params['address'] : '';
            $transfers['user_id'] = $user_id;
            $transfers['text'] = isset($params['remark']) ? $params['remark'] : '';
            $transfers['createtime'] = $createtime;
            $total_num = 0;
            //生成调拨数据
            db::beginTransaction();    //开启事务
            $transfers_id = DB::table('goods_transfers')->insertGetId($transfers);
            $customskus = array();
            if($transfers_id){
                foreach ($params['skuData'] as $val){
                    $customskus[] = $val['custom_sku'];
                    $detail['order_no'] = $transfers['order_no'];
                    $detail['custom_sku'] = $val['custom_sku'];
                    $spu = DB::table('self_custom_sku')->where('custom_sku',$val['custom_sku'])->orWhere('old_custom_sku',$val['custom_sku'])->select('spu','spu_id','cloud_num','id as custom_sku_id')->first();
                    if(!empty($spu)){
                        $balance = 0;//库存数量
                        $balance_num = 0;//预减后库存数量
                        if($params['out_house']==1){
                            $inventory =  DB::table('saihe_inventory')->where('custom_sku_id',$spu->custom_sku_id)->whereIn('warehouse_id',[2,386])->select('good_num')->get();
                            if(!empty($inventory)){
                                foreach ($inventory as $inv){
                                    $balance += $inv->good_num;
                                }
                            }
                        }
                        if($params['out_house']==2){
                            $inventory =  DB::table('saihe_inventory')->where('custom_sku_id',$spu->custom_sku_id)->where('warehouse_id',560)->select('good_num')->first();
                            if(!empty($inventory)){
                                $balance = $inventory->good_num;
                            }
                        }
                        if($params['out_house']==3){
                            $balance = $spu->cloud_num;
                        }
                        $request = DB::table('amazon_buhuo_detail as a')->leftJoin('amazon_buhuo_request as b','a.request_id','=','b.id')
                            ->whereIn('b.request_status',[3,4,5,6,7])
                            ->where('a.custom_sku_id',$spu->custom_sku_id)
                            ->where('b.warehouse',$params['out_house'])
                            ->select('a.courier_num','a.shipping_num','a.air_num')
                            ->get();
                        $request_num = 0;
                        if(!empty($request)){
                            foreach ($request as $r){
                                $request_num += $r->courier_num+$r->shipping_num+$r->air_num;
                            }
                            $balance_num = $balance-$request_num;
                        }

                        if($balance_num<$val['num']){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>$val['custom_sku'].'库存不足'];
                        }
                        $detail['spu'] = $spu->spu;
                    }else{
                        $detail['spu'] = '';
                    }
                    $detail['createtime'] = $createtime;
                    $detail['transfers_num'] = $val['num'];
                    $detail['spu_id'] = $spu->spu_id;
                    $detail['custom_sku_id'] = $spu->custom_sku_id;
                    if(isset($val['box_id'])){
                        $detail['box_id'] = $val['box_id'];
                    }
                    $total_num += $val['num'];
                    $insert_detail = DB::table('goods_transfers_detail')->insert($detail);
                }
                //计算调拨数量总数修改调拨数据
                $update =  DB::table('goods_transfers')->where('id',$transfers_id)->update(['total_num'=>$total_num]);
                if($params['in_house']==3){
                    //同步商品信息到云仓
                    $cloud = new \App\Http\Controllers\Jobs\CloudHouseController();
                    try {
                        $cloud::sendJob($customskus);
                    } catch(\Exception $e){
                        db::rollback();// 回调
                        Redis::Lpush('send_goods_fail',json_encode($params));
                    }
                }
                db::commit();
                return ['code'=>200,'msg'=>'调拨计划提交成功'];
            }else{
                db::rollback();// 回调
                return ['code'=>500,'msg'=>'新增调拨单失败'];
            }
        }else{
            db::rollback();// 回调
            return ['code'=>500,'msg'=>'数据类型错误'];
        }
    }

    public function goods_transfers_back($params){
        $token = $params['token'];

        if(empty($params['skuData'])){
            return ['code'=>500,'msg'=>'请选择要调动的商品'];
        }

        if(empty($params['in_house'])){
            return ['code'=>500,'msg'=>'请选择入仓仓库'];
        }

        if(empty($params['yj_arrive_time'])){
            return ['code'=>500,'msg'=>'请填写预计到达时间'];
        }

        $user_data = DB::table('users')->where('token', '=', $token)->select('Id', 'account')->get();
        $user_id = $user_data[0]->Id;
        $createtime = date('Y-m-d H:i:s');

        if(is_array($params['skuData'])){
            $transfers['back_order_no'] = $params['back_order_no'];
            $transfers['order_no'] = rand(100,999).time();
            $transfers['total_num'] = 0;
            $transfers['sku_count'] = count($params['skuData']);
            $transfers['out_house'] = $params['out_house'];
            $transfers['in_house'] = $params['in_house'];
            $transfers['type'] = $params['type'];
            $transfers['yj_arrive_time'] = $params['yj_arrive_time'];
            $transfers['type_detail'] = $params['type_detail'];
            $transfers['address'] = isset($params['address'])? $params['address'] : '';
            $transfers['user_id'] = $user_id;
            $transfers['text'] = isset($params['remark']) ? $params['remark'] : '';
            $transfers['createtime'] = $createtime;
            $transfers['shop_id'] = isset($params['shop_id'])? $params['shop_id'] : 0;

            $total_num = 0;
            //生成调拨数据
            db::beginTransaction();    //开启事务
            $transfers_id = DB::table('goods_transfers')->insertGetId($transfers);
//            $customskus = array();
            if($transfers_id){
                $contract_no = array();
                foreach ($params['skuData'] as $val){
                    if(!empty($val['contract_no'])){
                        $contract_no[] = $val['contract_no'];
                        $detail['contract_no'] = $val['contract_no'];
                    }
//                    $customskus[] = $val['custom_sku'];
                    $detail['order_no'] = $transfers['order_no'];
                    $detail['custom_sku'] = $val['custom_sku'];
                    $spu = DB::table('self_custom_sku')->where('custom_sku',$val['custom_sku'])->orWhere('old_custom_sku',$val['custom_sku'])->select('spu','spu_id','cloud_num','id as custom_sku_id','tongan_inventory','quanzhou_inventory')->first();
                    if(!empty($spu)){
                        $balance = 0;//库存数量
                        $balance_num = 0;//预减后库存数量
                        if($params['out_house']==1){
                            $balance = $spu->tongan_inventory;
                        }
                        if($params['out_house']==2){
                            $balance = $spu->quanzhou_inventory;
                        }
                        if($params['out_house']==3){
                            $balance = $spu->cloud_num;
                        }
                        $request = DB::table('amazon_buhuo_detail as a')->leftJoin('amazon_buhuo_request as b','a.request_id','=','b.id')
                            ->whereIn('b.request_status',[3,4,5,6,7])
                            ->where('a.custom_sku_id',$spu->custom_sku_id)
                            ->where('b.warehouse',$params['out_house'])
                            ->select('a.courier_num','a.shipping_num','a.air_num')
                            ->get();
                        $request_num = 0;
                        if(!empty($request)){
                            foreach ($request as $r){
                                $request_num += $r->courier_num+$r->shipping_num+$r->air_num;
                            }
                            $balance_num = $balance-$request_num;
                        }

                        if($balance_num<$val['num']){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>$val['custom_sku'].'库存不足'];
                        }
                        $detail['spu'] = $spu->spu;
                    }else{
                        $detail['spu'] = '';
                    }
                    $detail['createtime'] = $createtime;
                    $detail['transfers_num'] = $val['num'];
                    $detail['spu_id'] = $spu->spu_id;
                    $detail['custom_sku_id'] = $spu->custom_sku_id;
                    if(isset($val['box_id'])){
                        $detail['box_id'] = $val['box_id'];
                    }
                    $total_num += $val['num'];
                    $insert_detail = DB::table('goods_transfers_detail')->insert($detail);
                }
                $contract_no = array_unique($contract_no);
                $contract_no = json_encode($contract_no);
                //计算调拨数量总数修改调拨数据
                $update =  DB::table('goods_transfers')->where('id',$transfers_id)->update(['total_num'=>$total_num,'contract_no'=>$contract_no]);
                db::commit();
                return ['code'=>200,'msg'=>'提交成功'];
            }else{
                db::rollback();// 回调
                return ['code'=>500,'msg'=>'提交失败'];
            }
        }else{
            db::rollback();// 回调
            return ['code'=>500,'msg'=>'数据类型错误'];
        }
    }

    public function transfers_data($params){

        $page = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;
        $type = isset($params['type']) ? $params['type'] : 1;
        //分页
        $pagenNum=$page-1;
        if ($pagenNum != 0) {
            $pagenNum = $limit * $pagenNum;
            $limit=$limit*$page;
        }
        // DB::connection()->enableQueryLog();


        $list = DB::table('goods_transfers')->whereIn('type',$type);

        if(!empty($params['in_house'])){
            $list = $list->whereIn('in_house',$params['in_house']);
        }

        if(!empty($params['out_house'])){
            $list = $list->whereIn('out_house',$params['out_house']);
        }

        if(!empty($params['order_no'])){
            $list = $list->where('order_no','like','%'.$params['order_no'].'%');
        }

        if(!empty($params['contract_no'])){
            $list = $list->where('contract_no','like','%'.$params['contract_no'].'%');
        }

        if(!empty($params['user_id'])){
            $list = $list->where('user_id',$params['user_id']);
        }

        if(!empty($params['status'])){
            $list = $list->where('is_push',$params['status']);
        }

        if(!empty($params['start_time'])&&!empty($params['end_time'])){
            $list = $list->whereBetween('yj_arrive_time', [$params['start_time'], $params['end_time']]);
        }

        $count=$list->count();//数据总数
        //执行查询
        $data = $list
            ->offset($pagenNum)
            ->limit($limit)
            ->orderBy('yj_arrive_time','desc')
            ->get()
            ->toArray();

        // var_dump(DB::getQueryLog());
        foreach ($data as $v){
            $account = $this->GetUsers($v->user_id);
            if($account){
                $v->account = $account['account'];
            }
        }

        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }

    public function transfers_data_detail($params){
        if(!empty($params['order_no'])){
//            $page = isset($params['page']) ? $params['page'] : 1;
//            $limit = isset($params['limit']) ? $params['limit'] : 30;
//            //分页
//            $pagenNum=$page-1;
//            if ($pagenNum != 0) {
//                $pagenNum = $limit * $pagenNum;
//                $limit=$limit*$page;
//            }
            $list = DB::table('goods_transfers_detail as a');
            if(!empty($params['custom_sku'])){
                $list = $list->where('a.custom_sku','like','%'.$params['custom_sku'].'%');
            }
            if(!empty($params['contract_no'])){
                $list = $list->where('a.contract_no','like','%'.$params['contract_no'].'%');
            }
            $count =  $list->where('a.order_no',$params['order_no'])->count();

            $data = $list
                ->leftJoin('self_spu as b','a.spu_id','=','b.id')
                ->leftJoin('self_custom_sku as c','a.custom_sku_id','=','c.id')
                ->leftJoin('self_color_size as d','c.color_id','=','d.id')
                ->where('a.order_no',$params['order_no'])
//                ->offset($pagenNum)
//                ->limit($limit)
                ->select('a.contract_no','a.custom_sku','a.transfers_num','a.receive_num','a.createtime','b.spu','b.old_spu','d.name','d.identifying','c.size','a.custom_sku_id','a.spu_id')
                ->get()
                ->toArray();
            foreach ($data as $k=>$v){
                $data[$k]->img = $this->GetCustomskuImg($v->custom_sku);
            }
            return ['code'=>200,'msg'=>['data'=>$data,'totalNum' => $count]];
        }else{
            return ['code'=>500,'msg'=>'没有入库单id'];
        }
    }

    public function goods_cloud($params){

        if(!empty($params['order_no'])){
            $list = DB::table('goods_transfers as a')->leftJoin('goods_transfers_detail as b','a.order_no','=','b.order_no')
                ->where('a.order_no',$params['order_no'])
                ->select('b.custom_sku','b.transfers_num','b.order_no','a.type_detail','a.out_house','a.text')
                ->get()
                ->toArray();

            if(!empty($list)){
                $detail = [];
                foreach ($list as $v){
                    $detail[]= ['custom_sku'=> $v->custom_sku,'num'=>$v->transfers_num];
                }
            }else{
                return ['code'=>500,'msg'=>'此入库单没有入库数据'];
            }

            $type_detail = $list[0]->type_detail;
            $quequeres['order_no'] = $list[0]->order_no;
            $quequeres['detail'] = $detail;

            $params['out_house'] = $list[0]->out_house;

            if($params['in_house']==3){
                $quequeres['type'] = 'PTCK';
                $quequeres['text'] = $list[0]->text;
                //云仓则加入队列
                $cloud = new \App\Http\Controllers\Jobs\CloudHouseController();
                try {
                    $cloud::inhouseJob($quequeres);
                } catch(\Exception $e){
                    Redis::Lpush('in_house_fail',json_encode($quequeres));
                }
            }

            if($params['in_house']==5){
                if($params['out_house']==3){
//                    if($type_detail==7||$type_detail==8||$type_detail==10||$type_detail==11||$type_detail==9){
                    $quequeres['type'] = 'PTCK';
                    $quequeres['text'] = $list[0]->text;
//                    }
                    //云仓则加入队列
                    $cloud = new \App\Http\Controllers\Jobs\CloudHouseController();
                    try {
                        $cloud::outhouseJob($quequeres);
                    } catch(\Exception $e){
                        Redis::Lpush('out_house_fail',json_encode($quequeres['order_no']));
                    }
                }else{
                    DB::table('goods_transfers')->where('order_no',$params['order_no'])->update(['is_push'=>2]);
                }
            }
            if($params['in_house']==1||$params['in_house']==2||$params['in_house']==4||$params['in_house']==6){
                if($params['out_house']==3){
//                    if($type_detail==7||$type_detail==8||$type_detail==10||$type_detail==11||$type_detail==9){
                    $quequeres['type'] = 'PTCK';
                    $quequeres['text'] = $list[0]->text;
//                    }
                    //云仓则加入队列
                    $cloud = new \App\Http\Controllers\Jobs\CloudHouseController();
                    try {
                        $cloud::outhouseJob($quequeres);
                    } catch(\Exception $e){
                        Redis::Lpush('out_house_fail',json_encode($quequeres['order_no']));
                    }
                }else{
                    DB::table('goods_transfers')->where('order_no',$params['order_no'])->update(['is_push'=>2]);
                }
            }
            return  ['code'=>200,'msg'=>'推送成功'];
        }else{
            return ['code'=>500,'msg'=>'没有入库单id'];
        }
    }

    public function goods_cloudOut($params)
    {
        if (!empty($params['request_id'])) {
            $list = DB::table('amazon_buhuo_request')->where('id', $params['request_id'])->select('id','request_status')->first();
            if (!empty($list)) {
                if ($list->request_status < 5) {
                    $arr['order_no'] = $params['request_id'];
                    $arr['type'] = 'B2BCK';
                    $cloud = new \App\Http\Controllers\Jobs\CloudHouseController();
                    try {
                        $cloud::outhouseJob($arr);
                        Redis::Lpush('cloud_outRequest', $params['request_id']);
                        return ['code' => 200, 'msg' => '推送成功'];
                    } catch (\Exception $e) {
                        Redis::Lpush('out_house_fail', $params['request_id']);
                        return ['code' => 500, 'msg' => '推送失败'];
                    }
                } else {
                    return ['code' => 500, 'msg' => '云仓已接收此出库数据'];
                }
            } else {
                return ['code' => 500, 'msg' => '此出库单没有出库数据'];
            }

        }
    }

    public function getSaiheInventoryBySpu($params){

        $house_id = isset($params['house_id'])? $params['house_id'] : 1;
        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        //分页
        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        //初始化查询
        $thisDb = Db::table('self_spu');
        if(isset($params['spu'])){
            $spu = $params['spu'];
            $thisDb = $thisDb->where('spu','like','%'. $spu.'%')->orWhere('old_spu','like','%'. $spu.'%');
        }
        if(isset($params['one_cate_id'])){
            $thisDb = $thisDb->where('one_cate_id','=',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])){
            $thisDb = $thisDb->where('two_cate_id','=',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])){
            $thisDb = $thisDb->where('three_cate_id','=',$params['three_cate_id']);
        }
        //数据总数
        $count=$thisDb->count();

        //执行查询
//        DB::connection()->enableQueryLog();
        $data = $thisDb->offset($page)->limit($limit)
            ->select('spu','one_cate_id','two_cate_id','three_cate_id','old_spu','user_id')
            ->orderby('spu','ASC')
            ->groupBy('spu')
            ->get()
            ->toArray();


//        var_dump(DB::getQueryLog());

        //取出新旧库存spu组合成数组
        $spuArr = array_column($data,'spu');


        //根据组合的库存sku数组查询赛盒同安fba仓，同安自发货仓，泉州仓库存
        if($house_id==1){
            $warehouse_id = [2,386];
        }else{
            $warehouse_id = [560];
        }
        $inventory = DB::table('saihe_inventory')->whereIn('spu',$spuArr)->whereIn('warehouse_id',$warehouse_id)->select('spu','good_num','warehouse_id')->get()->toArray();
        $fbaArr = [];
        $selfArr = [];
        $quanzhouArr = [];
        $newdata = [];
        //三个仓的库存分别组成数组
//        foreach ($inventory as $invenVal){
//            if($house_id==1){
//                if($invenVal->warehouse_id==2){
//                    $fbaArr[$invenVal->spu] = 0;
//                }
//                if($invenVal->warehouse_id==386){
//                    $selfArr[$invenVal->spu] = 0;
//                }
//            }else{
//                if($invenVal->warehouse_id==560){
//                    $quanzhouArr[$invenVal->spu] = 0;
//                }
//            }
//        }

        foreach ($inventory as $invenVal){
            if($house_id==1){
                if($invenVal->warehouse_id==2){
                    if(isset($fbaArr[$invenVal->spu])){
                        $fbaArr[$invenVal->spu] += $invenVal->good_num;
                    }else{
                        $fbaArr[$invenVal->spu] = $invenVal->good_num;
                    }
                }
                if($invenVal->warehouse_id==386){
                    if(isset($selfArr[$invenVal->spu])){
                        $selfArr[$invenVal->spu] += $invenVal->good_num;
                    }else{
                        $selfArr[$invenVal->spu] = $invenVal->good_num;
                    }
                    $selfArr[$invenVal->spu] += $invenVal->good_num;
                }
            }else{
                if($invenVal->warehouse_id==560){
                    if(isset($quanzhouArr[$invenVal->spu])){
                        $quanzhouArr[$invenVal->spu] += $invenVal->good_num;
                    }else{
                        $quanzhouArr[$invenVal->spu] = $invenVal->good_num;
                    }
                }
            }
        }

        //查询库存sku的补货计划
        $buhuo_request = Db::table('amazon_buhuo_request as a')
            ->join('amazon_buhuo_detail as b','a.id','=','b.request_id')
            ->whereIn('b.spu',$spuArr)
            ->whereIn('a.request_status',[3,4,5,6,7])
            ->where('a.warehouse',$house_id)
            ->select('b.request_num','b.custom_sku', 'b.transportation_mode_name', 'b.transportation_type')
            ->get()->toArray();
        $courierArr = [];
        $shippingArr = [];
        $airArr = [];
        $kahangArr = [];
        $railwayArr = [];
        // 五种快递方式分别组成数组
        foreach ($buhuo_request as $requestVal){
            switch ($requestVal->transportation_type){
                case 1:
                    // 海运
                    if(isset($shippingArr[$requestVal->custom_sku])){
                        $shippingArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $shippingArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 2:
                    // 快递
                    if(isset($courierArr[$requestVal->custom_sku])){
                        $courierArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $courierArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 3:
                    // 空运
                    if(isset($airArr[$requestVal->custom_sku])){
                        $airArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $airArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 4:
                    // 铁路
                    if(isset($railwayArr[$requestVal->custom_sku])){
                        $railwayArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $railwayArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                case 5:
                    // 卡航
                    if(isset($kahangArr[$requestVal->custom_sku])){
                        $kahangArr[$requestVal->custom_sku] += $requestVal->request_num;
                    }else{
                        $kahangArr[$requestVal->custom_sku] = $requestVal->request_num;
                    }
                    break;
                default:
                    break;
            }
        }
        //赋值
        foreach ($data as $key=>$val){
            //大类
            $val->one_cate = '';
            $one_cate = $this->GetCategory($val->one_cate_id);
            if($one_cate){
                $val->one_cate = $one_cate['name'];
            }
            //二类
            $val->two_cate = '';
            $two_cate = $this->GetCategory($val->two_cate_id);
            if($two_cate){
                $val->two_cate = $two_cate['name'];
            }
            //三类
            $val->three_cate = '';
            $three_cate = $this->GetCategory($val->three_cate_id);
            if($three_cate){
                $val->three_cate = $three_cate['name'];
            }

            //图片
            $data[$key]->img = $this->GetSpuImg($val->spu);

            //运营
            $data[$key]->user = '';
            $account = $this->GetUsers($val->user_id);
            if($account){
                $data[$key]->user = $account['account'];
            }


            $data[$key]->inventory = 0;//库存

            //库存赋值
            if($house_id==1){
                if(array_key_exists($val->spu,$fbaArr)){
                    $fba_inventory = $fbaArr[$val->spu];
                }elseif(array_key_exists($val->old_spu,$fbaArr)){
                    $fba_inventory = $fbaArr[$val->old_spu];
                }else{
                    $fba_inventory = 0;
                }
                if(array_key_exists($val->spu,$selfArr)){
                    $self_inventory = $selfArr[$val->spu];
                }elseif(array_key_exists($val->old_spu,$selfArr)){
                    $self_inventory = $selfArr[$val->old_spu];
                }else{
                    $self_inventory = 0;
                }
                $data[$key]->inventory = $fba_inventory+$self_inventory;
            }else{
                if(array_key_exists($val->spu,$quanzhouArr)){
                    $data[$key]->inventory = $quanzhouArr[$val->spu];
                }elseif(array_key_exists($val->old_spu,$quanzhouArr)){
                    $data[$key]->inventory = $quanzhouArr[$val->old_spu];
                }else{
                    $data[$key]->inventory = 0;
                }
            }


            //快递计划发货数量赋值
            if(array_key_exists($val->spu,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->spu];
            }elseif(array_key_exists($val->old_spu,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->old_spu];
            }else{
                $data[$key]->courier_num = 0;
            }
            //海运计划发货数量赋值
            if(array_key_exists($val->spu,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->spu];
            }elseif(array_key_exists($val->old_spu,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->old_spu];
            }else{
                $data[$key]->shipping_num = 0;
            }
            //空运计划发货数量赋值
            if(array_key_exists($val->spu,$airArr)){
                $data[$key]->air_num = $airArr[$val->spu];
            }elseif(array_key_exists($val->old_spu,$airArr)){
                $data[$key]->air_num = $airArr[$val->old_spu];
            }else{
                $data[$key]->air_num = 0;
            }
            // 铁路计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$railwayArr)){
                $data[$key]->railway_num = $railwayArr[$val->custom_sku];
            }else{
                $data[$key]->railway_num = 0;
            }
            // 卡航计划发货数量赋值
            if(array_key_exists($val->old_custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->old_custom_sku];
            }elseif(array_key_exists($val->custom_sku,$kahangArr)){
                $data[$key]->kahang_num = $kahangArr[$val->custom_sku];
            }else{
                $data[$key]->kahang_num = 0;
            }
            //计划发货数量合计
            $data[$key]->total_num = $data[$key]->courier_num+$data[$key]->shipping_num+$data[$key]->air_num+$data[$key]->railway_num+$data[$key]->kahang_num;
            //库存周转天数
            $data[$key]->turnover =  $data[$key]->total_num>0 ? round( $data[$key]->inventory/$data[$key]->total_num,2) : 0;

            $data[$key]->spu = $val->old_spu??$val->spu;



            $newdata[$key]['spu'] =  $data[$key]->spu;
            $newdata[$key]['inventory'] = $data[$key]->inventory;
            $newdata[$key]['air_num'] = $data[$key]->air_num;
            $newdata[$key]['courier_num'] =  $data[$key]->courier_num;
            $newdata[$key]['shipping_num'] =  $data[$key]->shipping_num;
            $newdata[$key]['total_num'] = $data[$key]->total_num;
            $newdata[$key]['turnover'] =  $data[$key]->turnover;

        }

        $posttype = isset($params['posttype']) ? $params['posttype'] : 1;


        if($house_id==1){
            $table_name = '同安';
        }else{
            $table_name = '泉州';
        }

        if($posttype==2){
            if(count($data)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']=$table_name.'SPU报表';
            $p['title_list']  = [
                'spu'=>'SPU',
                'inventory' => '库存',
                'air_num' => '空运',
                'courier_num' => '快递',
                'shipping_num' => '海运',
                'total_num' => '合计',
                'turnover' => '周转天数',
            ];
            $p['data'] =  $newdata;
            $this->excel_expord($p);
        }

        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }



    public function getSaiheInventoryByCate($params){

        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;
        //分页
        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        $house_id = $params['house_id'];

        if($house_id ==1){
            $cache = 'tongan';
        }else{
            $cache = 'quanzhou';
        }



        $list = Redis::Get('datacache:'.$cache.'InventoryCate');
        $newdata = Redis::Get('datacache:'.$cache.'InventoryCate_excel');
        if(!$list||!$newdata||isset($params['id'])||isset($params['cate_id'])&&$params['cate_id']!='undefined'){
            $thisDb = DB::table('self_category')->offset($page)->limit($limit);
            $type = 1;

            if(isset($params['id'])){
                $cate =  DB::table('self_category')->where('fid',$params['id'])->get()->toArray();
                $ids = array_column($cate,'Id');
                $thisDb ->whereIn('Id',$ids);
                $type = $cate[0]->type;
            }

            if(isset($params['cate_id'])&&$params['cate_id']!='undefined'){
                $thisDb = $thisDb->where('Id','=',$params['cate_id']);
                $list = $thisDb->get();
            }else{
                $list = $thisDb->where('type',$type)->get();
            }


            $count =  $thisDb->count();
            $newlist = [];
            $newdata = [];
            foreach ($list as $k => $v) {
                # code...
                if($v->type==1){
                    $v->hasChildren = true;
                    $query['one_cate_id'] = $v->Id;
                    $query['house_id'] = $house_id;
                    $res = $this->getSaiheInventoryByCateTotal($query);
                }
                if($v->type==2){
                    $v->hasChildren = true;
                    $query['two_cate_id'] = $v->Id;
                    $query['house_id'] = $house_id;
                    $res = $this->getSaiheInventoryByCateTotal($query);

                }
                if($v->type==3){
                    $v->hasChildren = false;
                    $query['three_cate_id'] = $v->Id;
                    $query['house_id'] = $house_id;
                    $res = $this->getSaiheInventoryByCateTotal($query);

                }

                $v->inventory = $res['inventory']??0;
                $v->air_num = $res['air_num']??0;
                $v->courier_num = $res['courier_num']??0;
                $v->shipping_num = $res['shipping_num']??0;
                $v->total_num = $res['total_num']??0;
                $v->turnover = $res['turnover']??0;

                $newdata[$k]['name'] = $v->name;
                $newdata[$k]['inventory'] = $v->inventory;
                $newdata[$k]['air_num'] = $v->air_num;
                $newdata[$k]['courier_num'] = $v->courier_num;
                $newdata[$k]['shipping_num'] = $v->shipping_num;
                $newdata[$k]['total_num'] = $v->total_num;
                $newdata[$k]['turnover'] = $v->turnover;

            }


            Redis::setex('datacache:'.$cache.'InventoryCate',3600*24,json_encode($list));
            Redis::setex('datacache:'.$cache.'InventoryCate_excel',3600*24,json_encode($newdata));
        }else{
            $list = json_decode($list,true);
            $newdata = json_decode($newdata);
            $count = count($list);
        }

        $posttype = isset($params['posttype']) ? $params['posttype'] : 1;


        if($house_id==1){
            $table_name = '同安';
        }else{
            $table_name = '泉州';
        }

        if($posttype==2){
            if(count($list)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']=$table_name.'分类报表';
            $p['title_list']  = [
                'name'=>'分类名',
                'inventory' => '库存',
                'air_num' => '空运',
                'courier_num' => '快递',
                'shipping_num' => '海运',
                'total_num' => '合计',
                'turnover' => '周转天数',
            ];
            $p['data'] =  $newdata;
            $this->excel_expord($p);
        }

        return [
            'data' => $list,
            'totalNum' => $count,
        ];

    }




    public function getSaiheInventoryByCateTotal($params){

        $house_id = isset($params['house_id'])? $params['house_id'] : 1;

        //根据组合的库存sku数组查询赛盒同安fba仓，同安自发货仓，泉州仓库存
        if(isset($params['one_cate_id'])){
            $query['one_cate_id'] = $params['one_cate_id'];
        }
        if(isset($params['two_cate_id'])){
            $query['two_cate_id'] = $params['two_cate_id'];
        }
        if(isset($params['three_cate_id'])){
            $query['three_cate_id'] = $params['three_cate_id'];
        }

        $res = DB::table('self_spu')->where($query)->get(['spu'])->toArray();

        $spuArr = array_column($res,'spu');


        if($house_id==1){
            $warehouse_id = [2,386];
        }else{
            $warehouse_id = [560];
        }


        $inventory = DB::table('saihe_inventory')->whereIn('spu',$spuArr)->whereIn('warehouse_id',$warehouse_id)->select('spu','good_num','warehouse_id')->get()->toArray();
        $inventorynum = 0;

        //三个仓的库存分别组成数组
        foreach ($inventory as $invenVal){
            $inventorynum+=$invenVal->good_num;
        }


        //查询库存sku的补货计划
        $buhuo_request = Db::table('amazon_buhuo_request as a')
            ->join('amazon_buhuo_detail as b','a.id','=','b.request_id')
            ->whereIn('b.spu',$spuArr)
            ->whereIn('a.request_status',[3,4,5,6,7])
            ->where('a.warehouse',$house_id)
            ->select('b.courier_num','b.shipping_num','b.air_num','b.spu')
            ->get()->toArray();
        $courier = 0;
        $shipping = 0;
        $air = 0;
        //三种快递方式分别组成数组
        foreach ($buhuo_request as $requestVal){

            $courier += $requestVal->courier_num;
            $shipping += $requestVal->shipping_num;
            $air += $requestVal->air_num;

        }


        //计划发货数量合计
        $total_num =$courier+ $shipping+ $air;
        //库存周转天数
        $turnover =  $total_num >0 ? round($inventorynum/ $total_num,2) : 0;

        $return['inventory'] = $inventorynum;
        $return['air_num'] = $air;
        $return['courier_num'] = $courier;
        $return['shipping_num'] = $shipping;
        $return['total_num'] = $total_num;
        $return['turnover'] = $turnover;
        return $return;
    }

    public function getInventoryByFasin($params){
        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        //分页
        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        //初始化查询
        $thisDb = Db::table('product_detail as a')->leftjoin('amazon_link_ranklist as b','a.parent_asin','=','b.father_asin');
        if(isset($params['fasin'])){
            $fasin = $params['fasin'];
            $thisDb = $thisDb->where('a.parent_asin','like','%'.$fasin.'%');
        }
        if(isset($params['user_id'])){
            $thisDb = $thisDb->where('b.user_id','=',$params['user_id']);
        }



        //执行查询
//        DB::connection()->enableQueryLog();
        $thisDb = $thisDb->offset($page)->limit($limit)
            ->select('a.parent_asin as fasin','b.user_id','a.in_stock_num','a.in_bound_num','a.transfer_num','a.spu')
            ->groupBy('a.parent_asin');

        //数据总数
        $count=$thisDb->count();

        $data =$thisDb->get()
            ->toArray();


//        var_dump(DB::getQueryLog());
        $fasinarr = [];
        foreach ( $data as $key => $value) {
            # code...
            $fasinarr[$value->fasin]['in_stock_num'] = 0;
            $fasinarr[$value->fasin]['in_bound_num'] = 0;
            $fasinarr[$value->fasin]['transfer_num'] = 0;
            $fasinarr[$value->fasin]['spu'] = [];
        }

        foreach ( $data as $key => $value) {
            # code...
            $fasinarr[$value->fasin]['in_stock_num'] += $value->in_stock_num;
            $fasinarr[$value->fasin]['in_bound_num'] += $value->in_bound_num;
            $fasinarr[$value->fasin]['transfer_num'] += $value->transfer_num;
            $fasinarr[$value->fasin]['user_id'] =  $value->user_id;
            $fasinarr[$value->fasin]['fasin'] = $value->fasin;
            $fasinarr[$value->fasin]['spu'][] = $this->GetNewSpu($value->spu);
        }

        //取出新旧库存spu组合成数组
        $fasinarr_a = array_column($fasinarr,'fasin');
        // //查询库存
        // $product_detail = Db::table('product_detail')->whereIn('spu',$spuArr)->select('spu','in_stock_num','in_bound_num','transfer_num')->get()->toArray();
        //查询库龄
        $inventory_aged = Db::table('amazon_inventory_aged')->whereIn('father_asin',$fasinarr_a)->select('father_asin','day_0_90','day_91_180','day_181_270','day_271_365','day_365')->get()->toArray();
        $inven_age = [];//库龄数组  sku=>五种库龄数值

        foreach ($inventory_aged as $aged){
            $inven_age[$aged->father_asin] = ['day_0_90'=>$aged->day_0_90,'day_91_180'=>$aged->day_91_180,'day_181_270'=>$aged->day_181_270,'day_271_365'=>$aged->day_271_365,'day_365'=>$aged->day_365];
        }

        $fasinarr =  array_values($fasinarr);


        foreach ($fasinarr as $key =>$val){

            $fasin = $val['fasin'];

            $spus =  $val['spu'];

            $cus = DB::table('self_custom_sku')->whereIn('spu',$spus)->where('img','!=','')->first();
            $img = '';
            if($cus){
                $img = $cus->img;
            }

            $fasinarr[$key]['img'] = $img;
            unset($fasinarr[$key]['spu']);
            //运营
            $user = '';
            $account = $this->GetUsers($val['user_id']);
            if($account){
                $user = $account['account'];
            }

            $fasinarr[$key]['user_name'] = $user;
            //库龄赋值
            if(array_key_exists($fasin,$inven_age)){
                $fasinarr[$key]['day_0_90'] = $inven_age[$fasin]['day_0_90'];
                $fasinarr[$key]['day_91_180'] = $inven_age[$fasin]['day_91_180'];
                $fasinarr[$key]['day_181_270'] = $inven_age[$fasin]['day_181_270'];
                $fasinarr[$key]['day_271_365'] = $inven_age[$fasin]['day_271_365'];
                $fasinarr[$key]['day_365'] = $inven_age[$fasin]['day_365'];

            }else{
                $fasinarr[$key]['day_0_90'] = 0;
                $fasinarr[$key]['day_91_180'] = 0;
                $fasinarr[$key]['day_181_270'] = 0;
                $fasinarr[$key]['day_271_365'] = 0;
                $fasinarr[$key]['day_365'] = 0;
            }

        }
        $posttype = $params['posttype']??1;

        if($posttype==2){
            if(count($fasinarr)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']='海外仓fasin报表';
            $p['title_list']  = [
                'fasin'=>'父asin',
                'in_stock_num' => '可售',
                'in_bound_num' => '预留',
                'transfer_num' => '在途',
                'day_0_90' => '<90',
                'day_91_180' => '90-180',
                'day_181_270' => '180-270',
                'day_271_365' => '270',
                'day_365' => '>365',
            ];
            $p['data'] =  $fasinarr;
            $this->excel_expord($p);
        }



        return [
            'data' => $fasinarr,
            'totalNum' => $count,
        ];
    }




    public function getInventoryBySpu($params){
        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        //分页
        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        //初始化查询
        $thisDb = Db::table('self_spu');
        if(isset($params['spu'])){
            $spu = $params['spu'];
            $thisDb = $thisDb->where('spu','like','%'. $spu.'%')->orWhere('old_spu','like','%'. $spu.'%');
        }
        if(isset($params['one_cate_id'])){
            $thisDb = $thisDb->where('one_cate_id','=',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])){
            $thisDb = $thisDb->where('two_cate_id','=',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])){
            $thisDb = $thisDb->where('three_cate_id','=',$params['three_cate_id']);
        }
        //数据总数
        $count=$thisDb->count();

        //执行查询
//        DB::connection()->enableQueryLog();
        $data = $thisDb->offset($page)->limit($limit)
            ->select('spu','one_cate_id','two_cate_id','three_cate_id','old_spu','user_id')
            ->orderby('spu','ASC')
            ->groupBy('spu')
            ->get()
            ->toArray();


//        var_dump(DB::getQueryLog());

        //取出新旧库存spu组合成数组
        $spuArr=array_column($data,'spu');


        //查询库存
        $product_detail = Db::table('product_detail')->whereIn('spu',$spuArr)->select('spu','in_stock_num','in_bound_num','transfer_num')->get()->toArray();
        //查询库龄
        $inventory_aged = Db::table('amazon_inventory_aged')->whereIn('spu',$spuArr)->select('spu','day_0_90','day_91_180','day_181_270','day_271_365','day_365')->get()->toArray();
        $inven = [];//库存数组  sku=>三种库存
        foreach ($product_detail as $detail){
            if(isset($inven[$detail->spu])){
                $inven[$detail->spu]['in_stock_num']+=$detail->in_stock_num;
                $inven[$detail->spu]['in_bound_num']+=$detail->in_bound_num;
                $inven[$detail->spu]['transfer_num']+=$detail->transfer_num;
            }else{
                $inven[$detail->spu] = ['in_stock_num'=>$detail->in_stock_num,'in_bound_num'=>$detail->in_bound_num,'transfer_num'=>$detail->transfer_num];
            }
        }
        $inven_age = [];//库龄数组  sku=>五种库龄数值

        foreach ($inventory_aged as $aged){
            if(isset($inven_age[$aged->spu])){
                $inven_age[$aged->spu]['day_0_90']+=$aged->day_0_90;
                $inven_age[$aged->spu]['day_91_180']+=$aged->day_91_180;
                $inven_age[$aged->spu]['day_181_270']+=$aged->day_181_270;
                $inven_age[$aged->spu]['day_271_365']+=$aged->day_271_365;
                $inven_age[$aged->spu]['day_365']+=$aged->day_365;
            }else{
                $inven_age[$aged->spu] = ['day_0_90'=>$aged->day_0_90,'day_91_180'=>$aged->day_91_180,'day_181_270'=>$aged->day_181_270,'day_271_365'=>$aged->day_271_365,'day_365'=>$aged->day_365];
            }

        }

        $newdata = [];
        foreach ($data as $key =>$val){
            //图片
            $data[$key]->img = $this->GetSpuImg($val->spu);

            //运营
            $data[$key]->user = '';
            $account = $this->GetUsers($val->user_id);
            if($account){
                $data[$key]->user = $account['account'];
            }

            //大类
            $val->one_cate = '';
            $one_cate = $this->GetCategory($val->one_cate_id);
            if($one_cate){
                $val->one_cate = $one_cate['name'];
            }
            //二类
            $val->two_cate = '';
            $two_cate = $this->GetCategory($val->two_cate_id);
            if($two_cate){
                $val->two_cate = $two_cate['name'];
            }
            //三类
            $val->three_cate = '';
            $three_cate = $this->GetCategory($val->three_cate_id);
            if($three_cate){
                $val->three_cate = $three_cate['name'];
            }
            //库存赋值
            if(array_key_exists($val->spu,$inven)){
                $data[$key]->in_stock_num = $inven[$val->spu]['in_stock_num'];
                $data[$key]->in_bound_num = $inven[$val->spu]['in_bound_num'];
                $data[$key]->transfer_num = $inven[$val->spu]['transfer_num'];
            }elseif(array_key_exists($val->old_spu,$inven)){
                $data[$key]->in_stock_num = $inven[$val->old_spu]['in_stock_num'];
                $data[$key]->in_bound_num = $inven[$val->old_spu]['in_bound_num'];
                $data[$key]->transfer_num = $inven[$val->old_spu]['transfer_num'];
            }else{
                $data[$key]->in_stock_num = 0;
                $data[$key]->in_bound_num = 0;
                $data[$key]->transfer_num = 0;
            }
            //库龄赋值
            if(array_key_exists($val->spu,$inven_age)){
                $data[$key]->day_0_90 = $inven_age[$val->spu]['day_0_90'];
                $data[$key]->day_91_180 = $inven_age[$val->spu]['day_91_180'];
                $data[$key]->day_181_270 = $inven_age[$val->spu]['day_181_270'];
                $data[$key]->day_271_365 = $inven_age[$val->spu]['day_271_365'];
                $data[$key]->day_365 = $inven_age[$val->spu]['day_365'];
            }elseif(array_key_exists($val->old_spu,$inven_age)){
                $data[$key]->day_0_90 = $inven_age[$val->old_spu]['day_0_90'];
                $data[$key]->day_91_180 = $inven_age[$val->old_spu]['day_91_180'];
                $data[$key]->day_181_270 = $inven_age[$val->old_spu]['day_181_270'];
                $data[$key]->day_271_365 = $inven_age[$val->old_spu]['day_271_365'];
                $data[$key]->day_365 = $inven_age[$val->old_spu]['day_365'];
            }else{
                $data[$key]->day_0_90 = 0;
                $data[$key]->day_91_180 = 0;
                $data[$key]->day_181_270 = 0;
                $data[$key]->day_271_365 = 0;
                $data[$key]->day_365 = 0;
            }

            if($val->old_spu!=null&&$val->old_spu!=''){
                $val->spu = $val->old_spu;
            }

            $newdata[$key]['spu'] =  $data[$key]->spu;
            $newdata[$key]['in_stock_num'] = $data[$key]->in_stock_num;
            $newdata[$key]['in_bound_num'] = $data[$key]->in_bound_num;
            $newdata[$key]['transfer_num'] = $data[$key]->transfer_num;
            $newdata[$key]['day_0_90'] = $data[$key]->day_0_90;
            $newdata[$key]['day_91_180'] = $data[$key]->day_91_180;
            $newdata[$key]['day_181_270'] = $data[$key]->day_181_270;
            $newdata[$key]['day_271_365'] = $data[$key]->day_271_365;
            $newdata[$key]['day_365'] = $data[$key]->day_365;

        }

        $posttype = isset($params['posttype']) ? $params['posttype'] : 1;

        if($posttype==2){
            if(count($data)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']='海外仓SPU报表';
            $p['title_list']  = [
                'spu'=>'SPU',
                'in_stock_num' => '可售',
                'in_bound_num' => '预留',
                'transfer_num' => '在途',
                'day_0_90' => '<90',
                'day_91_180' => '90-180',
                'day_181_270' => '180-270',
                'day_271_365' => '270',
                'day_365' => '>365',
            ];
            $p['data'] =  $newdata;
            $this->excel_expord($p);
        }


        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }



    public function getInventoryByCate($params){

        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;
        //分页
        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        $cache = 'haiwai';
        $list = Redis::get('datacache:'.$cache.'InventoryCate');
        $newdata = Redis::get('datacache:'.$cache.'InventoryCate_excel');
        if(!$list||!$newdata||isset($params['id'])||isset($params['cate_id'])&&$params['cate_id']!='undefined'){


            $thisDb = DB::table('self_category')->offset($page)->limit($limit);
            $type = 1;

            if(isset($params['id'])){
                $cate =  DB::table('self_category')->where('fid',$params['id'])->get()->toArray();
                $ids = array_column($cate,'Id');
                $thisDb ->whereIn('Id',$ids);
                $type = $cate[0]->type;
            }


            if(isset($params['cate_id'])&&$params['cate_id']!='undefined'){
                $thisDb = $thisDb->where('Id','=',$params['cate_id']);
                $list = $thisDb->get();
            }else{
                $list = $thisDb->where('type',$type)->get();
            }

            $count =  $thisDb->count();
            $newdata = [];

            foreach ($list as $k => $v) {
                # code...
                if($v->type==1){
                    $query['one_cate_id'] = $v->Id;
                    $res = $this->getInventoryByCateTotal($query);
                    $v->hasChildren = true;
                }
                if($v->type==2){
                    $query['two_cate_id'] = $v->Id;
                    $res = $this->getInventoryByCateTotal($query);
                    $v->hasChildren = true;

                }
                if($v->type==3){
                    $query['three_cate_id'] = $v->Id;
                    $res = $this->getInventoryByCateTotal($query);
                    $v->hasChildren = false;

                }
                $v->in_stock_num = $res['in_stock_num']??0;
                $v->in_bound_num = $res['in_bound_num']??0;
                $v->transfer_num = $res['transfer_num']??0;
                $v->day_0_90 = $res['day_0_90']??0;
                $v->day_91_180 = $res['day_91_180']??0;
                $v->day_181_270 = $res['day_181_270']??0;
                $v->day_271_365 = $res['day_271_365']??0;
                $v->day_365 = $res['day_365']??0;

                $newdata[$k]['name'] = $v->name;
                $newdata[$k]['in_stock_num'] = $v->in_stock_num;
                $newdata[$k]['in_bound_num'] = $v->in_bound_num;
                $newdata[$k]['transfer_num'] = $v->transfer_num;
                $newdata[$k]['day_0_90'] = $v->day_0_90;
                $newdata[$k]['day_91_180'] = $v->day_91_180;
                $newdata[$k]['day_181_270'] = $v->day_181_270;
                $newdata[$k]['day_271_365'] = $v->day_271_365;
                $newdata[$k]['day_365'] = $v->day_365 ;

            }
            Redis::setex('datacache:'.$cache.'InventoryCate',3600*24,json_encode($list));
            Redis::setex('datacache:'.$cache.'InventoryCate_excel',3600*24,json_encode($newdata));
        }else{
            $list = json_decode($list,true);
            $newdata = json_decode($newdata);
            $count = count($list);
        }
        $posttype = isset($params['posttype']) ? $params['posttype'] : 1;

        if($posttype==2){
            if(count($list)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']='海外仓SPU报表';
            $p['title_list']  = [
                'name'=>'分类名',
                'in_stock_num' => '可售',
                'in_bound_num' => '预留',
                'transfer_num' => '在途',
                'day_0_90' => '<90',
                'day_91_180' => '90-180',
                'day_181_270' => '180-270',
                'day_271_365' => '270',
                'day_365' => '>365',
            ];
            $p['data'] =  $newdata;
            $this->excel_expord($p);
        }
        return [
            'data' => $list,
            'totalNum' => $count,
        ];

    }


    public function getInventoryByCateTotal($params){

        $house_id = isset($params['house_id'])? $params['house_id'] : 1;

        //根据组合的库存sku数组查询赛盒同安fba仓，同安自发货仓，泉州仓库存
        if(isset($params['one_cate_id'])){
            $query['one_cate_id'] = $params['one_cate_id'];
        }
        if(isset($params['two_cate_id'])){
            $query['two_cate_id'] = $params['two_cate_id'];
        }
        if(isset($params['three_cate_id'])){
            $query['three_cate_id'] = $params['three_cate_id'];
        }

        $res = DB::table('self_spu')->where($query)->get(['spu','old_spu'])->toArray();

        $spuarr = array_column($res,'spu');
        $old_spuarr = array_column($res,'old_spu');

        $spuArr=array_merge($spuarr,$old_spuarr);

        //查询库存
        $product_detail = Db::table('product_detail')->whereIn('spu',$spuArr)->select('spu','in_stock_num','in_bound_num','transfer_num')->get()->toArray();
        //查询库龄
        $inventory_aged = Db::table('amazon_inventory_aged')->whereIn('spu',$spuArr)->select('spu','day_0_90','day_91_180','day_181_270','day_271_365','day_365')->get()->toArray();
        $in_stock_num = 0;
        $in_bound_num = 0;
        $transfer_num = 0;
        foreach ($product_detail as $detail){
            $in_stock_num +=$detail->in_stock_num;
            $in_bound_num +=$detail->in_bound_num;
            $transfer_num +=$detail->transfer_num;
        }

        $day_0_90 = 0;
        $day_91_180 = 0;
        $day_181_270 = 0;
        $day_271_365 = 0;
        $day_365 = 0;

        foreach ($inventory_aged as $aged){
            $day_0_90 +=$aged->day_0_90;
            $day_91_180 +=$aged->day_91_180;
            $day_181_270 +=$aged->day_181_270;
            $day_271_365 +=$aged->day_271_365;
            $day_365 +=$aged->day_365;

        }

        $return['in_stock_num'] = $in_stock_num;
        $return['in_bound_num'] = $in_bound_num;
        $return['transfer_num'] = $transfer_num;
        $return['day_0_90'] = $day_0_90;
        $return['day_91_180'] = $day_91_180;
        $return['day_181_270'] = $day_181_270;
        $return['day_271_365'] = $day_271_365;
        $return['day_365'] = $day_365;
        return $return;
    }



    public function getCloudInventoryBySpu($params){


        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        //分页
        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        //初始化查询
        $thisDb = Db::table('self_spu');
        if(isset($params['spu'])){
            $spu = $params['spu'];
            $thisDb = $thisDb->where('spu','like','%'. $spu.'%')->orWhere('old_spu','like','%'. $spu.'%');
        }
        if(isset($params['one_cate_id'])){
            $thisDb = $thisDb->where('one_cate_id','=',$params['one_cate_id']);
        }
        if(isset($params['two_cate_id'])){
            $thisDb = $thisDb->where('two_cate_id','=',$params['two_cate_id']);
        }
        if(isset($params['three_cate_id'])){
            $thisDb = $thisDb->where('three_cate_id','=',$params['three_cate_id']);
        }
        //数据总数
        $count=$thisDb->count();

        //执行查询
//        DB::connection()->enableQueryLog();
        $data = $thisDb->offset($page)->limit($limit)
            ->select('spu','one_cate_id','two_cate_id','three_cate_id','old_spu','user_id')
            ->orderby('spu','ASC')
            ->groupBy('spu')
            ->get()
            ->toArray();


//        var_dump(DB::getQueryLog());

        //取出新旧库存spu组合成数组
        $spuArr= array_column($data,'spu');

        //根据组合的库存sku数组查询赛盒同安fba仓，同安自发货仓，泉州仓库存

        //查询库存sku的补货计划
        $buhuo_request = Db::table('amazon_buhuo_request as a')
            ->join('amazon_buhuo_detail as b','a.id','=','b.request_id')
            ->whereIn('b.spu',$spuArr)
            ->whereIn('a.request_status',[3,4,5,6,7])
            ->where('a.warehouse',3)
            ->select('b.courier_num','b.shipping_num','b.air_num','spu')
            ->get()->toArray();
        $courierArr = [];
        $shippingArr = [];
        $airArr = [];
        $newdata = [];
        //三种快递方式分别组成数组

        foreach ($buhuo_request as $requestVal){
            if($requestVal->spu){
                if(isset($courierArr[$requestVal->spu])){
                    $courierArr[$requestVal->spu] += $requestVal->courier_num;
                }else{
                    $courierArr[$requestVal->spu] = $requestVal->courier_num;
                }
                if(isset($shippingArr[$requestVal->spu])){
                    $shippingArr[$requestVal->spu] += $requestVal->courier_num;
                }else{
                    $shippingArr[$requestVal->spu] = $requestVal->courier_num;
                }
                if(isset($airArr[$requestVal->spu])){
                    $airArr[$requestVal->spu] += $requestVal->courier_num;
                }else{
                    $airArr[$requestVal->spu] = $requestVal->courier_num;
                }
            }
        }
        //赋值
        foreach ($data as $key=>$val){

            //图片
            $data[$key]->img = $this->GetSpuImg($val->spu);

            //运营
            $data[$key]->user = '';
            $account = $this->GetUsers($val->user_id);
            if($account){
                $data[$key]->user = $account['account'];
            }

            //大类
            $val->one_cate = '';
            $one_cate = $this->GetCategory($val->one_cate_id);
            if($one_cate){
                $val->one_cate = $one_cate['name'];
            }
            //二类
            $val->two_cate = '';
            $two_cate = $this->GetCategory($val->two_cate_id);
            if($two_cate){
                $val->two_cate = $two_cate['name'];
            }
            //三类
            $val->three_cate = '';
            $three_cate = $this->GetCategory($val->three_cate_id);
            if($three_cate){
                $val->three_cate = $three_cate['name'];
            }

            $data[$key]->inventory = 0;//库存

            //库存

            $cus = Db::table('self_custom_sku')->where('spu',$val->old_spu)->orwhere('spu',$val->spu)->get();
            $inventory = 0;
            foreach ($cus as $cusres) {
                # code...
                $inventory+=$cusres->cloud_num;
            }

            $data[$key]->inventory =  $inventory;

            //快递计划发货数量赋值
            if(array_key_exists($val->spu,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->spu];
            }elseif(array_key_exists($val->old_spu,$courierArr)){
                $data[$key]->courier_num = $courierArr[$val->old_spu];
            }else{
                $data[$key]->courier_num = 0;
            }
            //海运计划发货数量赋值
            if(array_key_exists($val->spu,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->spu];
            }elseif(array_key_exists($val->old_spu,$shippingArr)){
                $data[$key]->shipping_num = $shippingArr[$val->old_spu];
            }else{
                $data[$key]->shipping_num = 0;
            }
            //空运计划发货数量赋值
            if(array_key_exists($val->spu,$airArr)){
                $data[$key]->air_num = $airArr[$val->spu];
            }elseif(array_key_exists($val->old_spu,$airArr)){
                $data[$key]->air_num = $airArr[$val->old_spu];
            }else{
                $data[$key]->air_num = 0;
            }
            //计划发货数量合计
            $data[$key]->total_num = $data[$key]->courier_num+$data[$key]->shipping_num+$data[$key]->air_num;
            //库存周转天数
            $data[$key]->turnover =  $data[$key]->total_num>0 ? round( $data[$key]->inventory/$data[$key]->total_num,2) : 0;

            $data[$key]->spu = $val->old_spu??$val->spu;

            $newdata[$key]['spu'] =  $data[$key]->spu;
            $newdata[$key]['inventory'] = $data[$key]->inventory;
            $newdata[$key]['air_num'] = $data[$key]->air_num;
            $newdata[$key]['courier_num'] =  $data[$key]->courier_num;
            $newdata[$key]['shipping_num'] =  $data[$key]->shipping_num;
            $newdata[$key]['total_num'] = $data[$key]->total_num;
            $newdata[$key]['turnover'] =  $data[$key]->turnover;

        }

        $posttype = isset($params['posttype']) ? $params['posttype'] : 1;


        if($posttype==2){
            if(count($data)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']='云仓SPU报表';
            $p['title_list']  = [
                'spu'=>'SPU',
                'inventory' => '库存',
                'air_num' => '空运',
                'courier_num' => '快递',
                'shipping_num' => '海运',
                'total_num' => '合计',
                'turnover' => '周转天数',
            ];
            $p['data'] =  $newdata;
            $this->excel_expord($p);
        }

        return [
            'data' => $data,
            'totalNum' => $count,
        ];
    }



    public function getCloudInventoryByCate($params){

        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;



        //分页
        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        $cache = "cloud";
        $list = Redis::Get('datacache:'.$cache.'InventoryCate');
        $newdata = Redis::Get('datacache:'.$cache.'InventoryCate_excel');
        if(!$list||!$newdata||isset($params['id'])||isset($params['cate_id'])&&$params['cate_id']!='undefined'){

            $thisDb = DB::table('self_category')->offset($page)->limit($limit);
            $type = 1;


            if(isset($params['id'])){
                $cate =  DB::table('self_category')->where('fid',$params['id'])->get()->toArray();
                $ids = array_column($cate,'Id');
                $thisDb ->whereIn('Id',$ids);
                $type = $cate[0]->type;
            }


            if(isset($params['cate_id'])&&$params['cate_id']!='undefined'){
                $thisDb = $thisDb->where('Id','=',$params['cate_id']);
                $list = $thisDb->get();
            }else{
                $list = $thisDb->where('type',$type)->get();
            }

            $count =  $thisDb->count();
            $newdata=[];
            foreach ($list as $k => $v) {
                # code...
                if($v->type==1){
                    $query['one_cate_id'] = $v->Id;
                    $res = $this->getCloudInventoryByCateTotal($query);
                    $v->hasChildren = true;
                }
                if($v->type==2){
                    $query['two_cate_id'] = $v->Id;
                    $res = $this->getCloudInventoryByCateTotal($query);
                    $v->hasChildren = true;

                }
                if($v->type==3){
                    $query['three_cate_id'] = $v->Id;
                    $res = $this->getCloudInventoryByCateTotal($query);
                    $v->hasChildren = false;

                }

                $v->inventory = $res['inventory']??0;
                $v->air_num = $res['air_num']??0;
                $v->courier_num = $res['courier_num']??0;
                $v->shipping_num = $res['shipping_num']??0;
                $v->total_num = $res['total_num']??0;
                $v->turnover = $res['turnover']??0;

                $newdata[$k]['name'] = $v->name;
                $newdata[$k]['inventory'] = $v->inventory;
                $newdata[$k]['air_num'] = $v->air_num;
                $newdata[$k]['courier_num'] = $v->courier_num;
                $newdata[$k]['shipping_num'] = $v->shipping_num;
                $newdata[$k]['total_num'] = $v->total_num;
                $newdata[$k]['turnover'] = $v->turnover;

            }
            Redis::setex('datacache:'.$cache.'InventoryCate',3600*24,json_encode($list));
            Redis::setex('datacache:'.$cache.'InventoryCate_excel',3600*24,json_encode($newdata));
        }else{
            $list = json_decode($list,true);
            $newdata = json_decode($newdata);
            $count = count($list);
        }
        $posttype = isset($params['posttype']) ? $params['posttype'] : 1;
        if($posttype==2){
            if(count($list)>10000){
                return ['type' => 'fail', 'msg' => '无法导出1万条以上数据'];
            }
            $p['title']='云仓分类报表';
            $p['title_list']  = [
                'name'=>'分类名',
                'inventory' => '库存',
                'air_num' => '空运',
                'courier_num' => '快递',
                'shipping_num' => '海运',
                'total_num' => '合计',
                'turnover' => '周转天数',
            ];
            $p['data'] =  $newdata;
            $this->excel_expord($p);
        }

        return [
            'data' => $list,
            'totalNum' => $count,
        ];

    }


    public function getCloudInventoryByCateTotal($params){

        //根据组合的库存sku数组查询赛盒同安fba仓，同安自发货仓，泉州仓库存
        if(isset($params['one_cate_id'])){
            $query['one_cate_id'] = $params['one_cate_id'];
        }
        if(isset($params['two_cate_id'])){
            $query['two_cate_id'] = $params['two_cate_id'];
        }
        if(isset($params['three_cate_id'])){
            $query['three_cate_id'] = $params['three_cate_id'];
        }

        $res = DB::table('self_spu')->where($query)->get(['spu','old_spu'])->toArray();

        $spuarr = array_column($res,'spu');
        $old_spuarr = array_column($res,'old_spu');

        $spuArr=array_merge($spuarr,$old_spuarr);




        $inventory = DB::table('self_custom_sku')->whereIn('spu',$spuArr)->select('spu','cloud_num')->get()->toArray();
        $inventorynum = 0;

        //三个仓的库存分别组成数组
        foreach ($inventory as $invenVal){
            $inventorynum+=$invenVal->cloud_num;
        }

        //查询库存sku的补货计划
        $buhuo_request = Db::table('amazon_buhuo_request as a')
            ->join('amazon_buhuo_detail as b','a.id','=','b.request_id')
            ->whereIn('b.spu',$spuArr)
            ->whereIn('a.request_status',[3,4,5,6,7])
            ->where('a.warehouse',3)
            ->select('b.courier_num','b.shipping_num','b.air_num','spu')
            ->get()->toArray();
        $courier = 0;
        $shipping = 0;
        $air = 0;
        //三种快递方式分别组成数组
        foreach ($buhuo_request as $requestVal){

            $courier += $requestVal->courier_num;
            $shipping += $requestVal->shipping_num;
            $air += $requestVal->air_num;

        }


        //计划发货数量合计
        $total_num =$courier+ $shipping+ $air;
        //库存周转天数
        $turnover =  $total_num >0 ? round($inventorynum/ $total_num,2) : 0;

        $return['inventory'] = $inventorynum;
        $return['air_num'] = $air;
        $return['courier_num'] = $courier;
        $return['shipping_num'] = $shipping;
        $return['total_num'] = $total_num;
        $return['turnover'] = $turnover;
        return $return;
    }


    public function confirmInhouse($params){
        $token = $params['token'];
        //根据token识别申请人
        $user_data = DB::table('users')->where('token', '=', $token)->select('Id')->first();
        $user_id = $user_data->Id;
        $order_no = $params['order_no'];
        $goods_transfers = DB::table('goods_transfers')->where('order_no',$order_no)->first();
        if(empty($goods_transfers)){
            return ['code'=>500,'msg'=>'没有此单号的数据'];
        }
        $data = $params['data'];
        db::beginTransaction();
        $receive_total = 0;
        $date = date('Y-m-d H:i:s');
        foreach ($data as $v){
            if($v['receive_num']!=0){
                $inven = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->select('factory_num','tongan_inventory','quanzhou_inventory','deposit_inventory')->first();
                if(empty($inven)){
                    db::rollback();// 回调
                    return ['code'=>500,'msg'=>'系统没有id是'.$v['custom_sku_id'].'的sku'];
                }

                $update = DB::table('goods_transfers_detail')->where('order_no',$order_no)->where('custom_sku_id',$v['custom_sku_id'])->update(['receive_num'=>$v['receive_num']]);
                if(!$update){
                    db::rollback();// 回调
                    return ['code'=>500,'msg'=>$order_no.'的'.$v['custom_sku'].'接收数据失败'];
                }

                $insert['order_no'] = $order_no;
                $insert['spu'] = $v['spu'];
                $insert['spu_id'] = $v['spu_id'];
                $insert['custom_sku'] = $v['custom_sku'];
                $insert['custom_sku_id'] = $v['custom_sku_id'];
                $insert['num'] = $v['receive_num'];
                $insert['createtime'] = $date;
                $insert['user_id'] = $user_id;

                if($goods_transfers->type==1){
                    if($goods_transfers->type_detail==2){
                        //采购入库确认出货数据
                        $insert['type'] = 1;
                        $insert['type_detail'] = 14;
                        $insert['warehouse_id'] = $goods_transfers->out_house;

                        $reduce_num = $inven->factory_num-$v['receive_num']>0 ? $inven->factory_num-$v['receive_num'] : 0;
                        $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['factory_num'=>$reduce_num]);

                        $insertOut = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertOut) {
                            db::rollback();// 回调
                            return ['code' => 500, 'msg' => '新增出库记录失败--' . $v['custom_sku']];
                        }

                        $insert['type'] = 2;
                        $insert['type_detail'] = 2;
                        $insert['warehouse_id'] = $goods_transfers->in_house;
                        if($goods_transfers->in_house==1){
                            $increase_num = $inven->tongan_inventory+$v['receive_num'];
                            $modify2 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==2){
                            $increase_num = $inven->quanzhou_inventory+$v['receive_num'];
                            $modify2 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==6){
                            $increase_num = $inven->deposit_inventory+$v['receive_num'];
                            $modify2 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }

                        $insertIn = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertIn){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>'新增入库记录失败--'.$v['custom_sku']];
                        }
                    }else{
                        //采购单退货
                        $insert['type'] = 1;
                        $insert['type_detail'] = 9;
                        $insert['warehouse_id'] = $goods_transfers->out_house;

                        if($goods_transfers->out_house==1){
                            $reduce_num = $inven->tongan_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==2){
                            $reduce_num = $inven->quanzhou_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==6){
                            $reduce_num = $inven->deposit_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }
//                        if($reduce_num<0){
//                            db::rollback();// 回调
//                            return ['code'=>500,'msg'=>$v['custom_sku'].'的库存不足'];
//                        }

                        $insertOut = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertOut) {
                            db::rollback();// 回调
                            return ['code' => 500, 'msg' => '新增出库记录失败--' . $v['custom_sku']];
                        }

                        $insertOut = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertOut){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>'新增出库记录失败--'.$v['custom_sku']];
                        }
                        $insert['type'] = 2;
                        $insert['type_detail'] = 13;
                        $insert['warehouse_id'] = $goods_transfers->in_house;
                        $insertIn = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertIn){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>'新增入库记录失败--'.$v['custom_sku']];
                        }
                    }
                }elseif($goods_transfers->type==2){
                    //订单出入库
                    if($goods_transfers->type_detail==12){
                        $insert['type'] = 1;
                        $insert['type_detail'] = $goods_transfers->type_detail;
                        $insert['warehouse_id'] = $goods_transfers->out_house;
                        if($goods_transfers->out_house==1){
                            $reduce_num = $inven->tongan_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==2){
                            $reduce_num = $inven->quanzhou_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==6){
                            $reduce_num = $inven->deposit_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }
                        $insertIn = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertIn){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>'新增出库记录失败--'.$v['custom_sku']];
                        }
                    }else{
                        $insert['type'] = 2;
                        $insert['type_detail'] = $goods_transfers->type_detail;
                        $insert['warehouse_id'] = $goods_transfers->in_house;
                        if($goods_transfers->in_house==1){
                            $reduce_num = $inven->tongan_inventory+$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==2){
                            $reduce_num = $inven->quanzhou_inventory+$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==6){
                            $reduce_num = $inven->deposit_inventory+$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }
                        $insertIn = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertIn){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>'新增出库记录失败--'.$v['custom_sku']];
                        }
                    }
                }elseif($goods_transfers->type==4){
                    //样品出入库
                    if($goods_transfers->type_detail==10){
                        $insert['type'] = 1;
                        $insert['type_detail'] = $goods_transfers->type_detail;
                        $insert['warehouse_id'] = $goods_transfers->out_house;
                        if($goods_transfers->out_house==1){
                            $reduce_num = $inven->tongan_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==2){
                            $reduce_num = $inven->quanzhou_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==6){
                            $reduce_num = $inven->deposit_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }
//                    if($reduce_num<0){
//                        db::rollback();// 回调
//                        return ['code'=>500,'msg'=>$v['custom_sku'].'的库存不足'];
//                    }
                        $insertOut = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertOut) {
                            db::rollback();// 回调
                            return ['code' => 500, 'msg' => '新增出库记录失败--' . $v['custom_sku']];
                        }
                    }else{
                        $insert['type'] = 2;
                        $insert['type_detail'] = $goods_transfers->type_detail;
                        $insert['warehouse_id'] = $goods_transfers->in_house;
                        if($goods_transfers->in_house==1){
                            $reduce_num = $inven->tongan_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==2){
                            $reduce_num = $inven->quanzhou_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==6){
                            $reduce_num = $inven->deposit_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }
                        $insertOut = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertOut) {
                            db::rollback();// 回调
                            return ['code' => 500, 'msg' => '新增入库记录失败--' . $v['custom_sku']];
                        }
                    }
                }elseif($goods_transfers->type==6){
                    //仓库调拨出入库
                    if($goods_transfers->type_detail==1){
                        $insert['type'] = 1;
                        $insert['type_detail'] = $goods_transfers->type_detail;
                        $insert['warehouse_id'] = $goods_transfers->out_house;
                        if($goods_transfers->out_house==1){
                            $reduce_num = $inven->tongan_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==2){
                            $reduce_num = $inven->quanzhou_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->out_house==6){
                            $reduce_num = $inven->deposit_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }
//                    if($reduce_num<0){
//                        db::rollback();// 回调
//                        return ['code'=>500,'msg'=>$v['custom_sku'].'的库存不足'];
//                    }
                        $insertOut = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertOut) {
                            db::rollback();// 回调
                            return ['code' => 500, 'msg' => '新增出库记录失败--' . $v['custom_sku']];
                        }
                    }else{
                        $insert['type'] = 2;
                        $insert['type_detail'] = $goods_transfers->type_detail;
                        $insert['warehouse_id'] = $goods_transfers->in_house;
                        if($goods_transfers->in_house==1){
                            $reduce_num = $inven->tongan_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['tongan_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==2){
                            $reduce_num = $inven->quanzhou_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['quanzhou_inventory'=>$increase_num]);
                        }elseif($goods_transfers->in_house==6){
                            $reduce_num = $inven->deposit_inventory-$v['receive_num'];
                            $modify1 = DB::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$increase_num]);
                        }
                        $insertOut = DB::table('cloudhouse_record')->insert($insert);
                        if(!$insertOut) {
                            db::rollback();// 回调
                            return ['code' => 500, 'msg' => '新增出库记录失败--' . $v['custom_sku']];
                        }
                    }

                }
                $receive_total += $v['receive_num'];
            }
        }
        $updateTotal = DB::table('goods_transfers')->where('order_no',$order_no)->update(['receive_total_num'=>$receive_total,'sj_arrive_time'=>$date,'is_push'=>3,'confirm_id'=>$user_id]);
        if(!$updateTotal){
            db::rollback();// 回调
            return ['code'=>500,'msg'=>'修改接收总数失败'];
        }
        db::commit();
        return ['code'=>200,'msg'=>'接收成功'];
    }

    /**
     * @Desc:采购详情导出
     * @param $params
     * @return true
     * @throws \Exception
     * @author: Liu Sinian
     * @Time: 2023/2/23 16:39
     */
    public function exportTransferDataDetail($params)
    {
        if (isset($params['order_no']) && !empty($params['order_no'])){
            $list = $this->goodTransfersDetailModel::query()->where('order_no',$params['order_no'])->orderBy('custom_sku_id','asc')->select()->get()->toArray();
            if (!empty($list)){
                $imgList = [];
                foreach ($list as &$item){
                    // 获取库存sku详情
                    $customSkuData = BaseModel::GetCustomSkus($item['custom_sku']);
                    // 获取颜色
                    $color = BaseModel::GetColorSize($customSkuData['color_id']);
                    $item['color'] = $color['name'].'/'.$color['identifying']; // 颜色
                    $item['size'] = $customSkuData['size']; // 尺寸
                    $customSku = $customSkuData['custom_sku'];
                    if ($customSkuData['old_custom_sku']){
                        $customSku = $customSkuData['old_custom_sku'];
                    }
                    $img =  $this->GetCustomskuImg($customSku); // 图片
                    if (!in_array($img, $imgList)){
                        $imgList[] = $img;
                        $item['img'] = $img;
                    }else{
                        $item['img'] = "";
                    }
                }
            }else{
                throw new \Exception('导出数据为空！');
            }
            // excel文件表头
            $excelName = '采购详情'.time();
            // 表头
            $excelTitle = [
                'contract_no' => 'contract_no',
                'custom_sku' => '库存sku',
                'img' => '图片',
                'spu' => 'SPU',
                'color' => '颜色',
                'size' => '尺码',
                'transfers_num' => '采购数量',
                'receive_num' => '实际接收数量',
                'createtime' => '创建时间'
            ];
            // 组织导出列表数据
            $excelData = [
                'data' => $list,
                'title_list' => $excelTitle,
                'title' => $excelName,
                'type' => '采购详情导出',
                'user_id' => $params['user_id']
            ];

        }else{
            throw new \Exception('未给定采购号');
        }

        return $excelData;
    }


    /**
     * @Desc: //修改待推送库存变动数据
     * @param $params
     * @return ['code'=>'','msg=>'']
     * @author: 严威
     * @Time: 2023/4/7 16:37
     * @updateTime: 2023/4/7 16:37
     */
    public function update_goods_transfers($params){
        $order_no =  isset($params['order_no'])?$params['order_no']:0;

        if($order_no!=0){
            if(is_array($params['data'])){
                $sku_count = 0;
                $total_num = 0;
                foreach ($params['data'] as $v){
                    if($v['transfers_num']==0){
                        DB::table('goods_transfers_detail')->where('order_no',$order_no)->where('custom_sku_id',$v['custom_sku_id'])->delete();
                    }else{
                        $sku_count++;
                    }
                    DB::table('goods_transfers_detail')->where('order_no',$order_no)->where('custom_sku_id',$v['custom_sku_id'])->update(['transfers_num'=>$v['transfers_num']]);
                    $total_num += $v['transfers_num'];
                }
                DB::table('goods_transfers')->where('order_no',$params['order_no'])->update(['total_num'=>$total_num,'sku_count'=>$sku_count]);
                return ['code'=>200,'msg'=>'修改成功'];
            }else{
                return ['code'=>500,'msg'=>'数据错误'];
            }
        }else{
            return ['code'=>500,'msg'=>'未获取到批号'];
        }
    }


    //生成中转箱码
    public function create_transfer_box($params){
        if(!isset($params['order_no'])){
            return ['code'=>500,'msg'=>'未获取到批号'];
        }else{
            $order_no = $params['order_no'];
        }
//        $remarks = isset($params['remarks']) ? $params['remarks'] : '';

        db::beginTransaction();    //开启事务

        $thisId = DB::table('goods_transfers_box')->insertGetId(['order_no'=>$order_no]);

        if($thisId){
            $box_code = 'bc'.$thisId;
            $update = DB::table('goods_transfers_box')->where('id',$thisId)->update(['box_code'=>$box_code]);
            if($update){
                db::commit();
                return ['code'=>200,'msg'=>'新增成功'];
            }else{
                db::rollback();// 回调
                return ['code'=>500,'msg'=>'新增中装箱失败'];
            }
        }else{
            db::rollback();// 回调
            return ['code'=>500,'msg'=>'新增中装箱失败'];
        }
    }


    public function delete_transfer_box($params){
        $id = $params['id'];
        $detail = DB::table('goods_transfers_box_detail')->where('box_id',$id)->get();
        if($detail->isEmpty()){
            DB::table('goods_transfers_box')->where('id',$id)->delete();
            DB::table('goods_transfers_box_detail')->where('box_id',$id)->delete();
            return ['code'=>200,'msg'=>'删除成功'];
        }else{
            return ['code'=>200,'msg'=>'删除失败，不能删除已有数据的中转箱'];
        }

    }

    //批次中转箱列表
    public function transfers_box_list($params){

        $order_no =  isset($params['order_no'])?$params['order_no']:0;

        $list = DB::table('goods_transfers_box')->where('order_no',$order_no)->select('id','box_code','box_num','onshelf_num')->get()->toArray();

        return $list;
    }

    //新增中转箱货物信息
    public function update_transfers_box($params){
        if(!isset($params['id'])){
            return ['code'=>500,'msg'=>'未获取到中转任务id'];
        }else{
            $id = $params['id'];
            $box = DB::table('goods_transfers_box')->where('id',$id)->select('order_no')->first();
            if(empty($box)){
                return ['code'=>500,'msg'=>'没有此中转任务id'];
            }
        }


        if(is_array($params['data'])){
            db::beginTransaction();    //开启事务
            $num = 0;
            foreach ($params['data'] as $v){
                $insert['custom_sku_id'] = $this->GetCustomSkuId($v['custom_sku']);

                $transfers_detail = DB::table('goods_transfers_detail')->where('order_no',$box->order_no)->where('custom_sku_id',$insert['custom_sku_id'])->select('id')->first();
                if(empty($transfers_detail)){
                    return ['code'=>500,'msg'=>'清单中没有此产品，无法添加到中转任务'];
                }
                $sku_detail = DB::table('goods_transfers_box_detail')->where('box_id',$id)->where('custom_sku_id',$insert['custom_sku_id'])->first();
                if(!empty($sku_detail)){
                    $update = DB::table('goods_transfers_box_detail')->where('id',$sku_detail->id)->update(['box_num'=>$v['box_num']+$sku_detail->box_num]);
                    $num+=$v['box_num'];
                }else{
                    $insert['box_id'] = $id;
                    $insert['box_num'] = $v['box_num'];
                    $insert['custom_sku'] = $v['client_sku'];
                    $add = DB::table('goods_transfers_box_detail')->insert($insert);
                    if(!$add){
                        db::rollback();// 回调
                        return ['code'=>500,'msg'=>'新增货物数据失败'];
                    }else{
                        $num+=$v['box_num'];
                    }
                }

            }
            $box_data = DB::table('goods_transfers_box')->where('id',$id)->select('box_num')->first();
            if(!empty($box_data)){
                DB::table('goods_transfers_box')->where('id',$id)->update(['box_num'=>$num+$box_data->box_num]);
            }else{
                DB::table('goods_transfers_box')->where('id',$id)->update(['box_num'=>$num]);
            }

            db::commit();
            return ['code'=>200,'msg'=>'输入货物信息成功'];
        }else{
            return ['code'=>500,'msg'=>'数据错误'];
        }

    }

    //显示中转箱数据明细
    public function transfers_box_detail($params){
        if(!isset($params['id'])){
            return ['code'=>500,'msg'=>'未获取到中转箱id'];
        }else{
            $id = $params['id'];
        }
        $detail = DB::table('goods_transfers_box_detail')->where('box_id',$id)->get();
        if(!empty($detail)){
            foreach ($detail as $k=>$v){
                $detail[$k]->img = $this->GetCustomskuImg($v->custom_sku);
                $detail[$k]->location_code = $this->Getlocation($v->location_ids)?$this->Getlocation($v->location_ids)['location_code']:'';
            }
        }

        return $detail;
    }

    //库位上架商品库存
    public function location_change($params){

        $user_data = DB::table('users')->where('token', '=', $params['token'])->select('Id', 'account')->first();
        if(empty($user_data)){
            return ['code'=>500,'msg'=>'该账号异常，无法操作'];
        }

        if(isset($params['type'])){
            $type = $params['type'];
        }else{
            return ['code'=>500,'msg'=>'未获取到库位数据变动类型'];
        }

        if(isset($params['warehouse_id'])){
            $warehouse_id = $params['warehouse_id'];
        }else{
            return ['code'=>500,'msg'=>'未获取到仓库id'];
        }

        if(isset($params['location_code'])){
            $location_code = $params['location_code'];
        }else{
            return ['code'=>500,'msg'=>'未获取到库位编码'];
        }

        if(isset($params['task_code'])){
            $boxData = DB::table('goods_transfers_box')->where('box_code',$params['task_code'])->select('id','onshelf_num','order_no')->first();
            if(!empty($boxData)){
                $box_id = $boxData->id;
                $onshelf_num = $boxData->onshelf_num;
                $order_no = $boxData->order_no;
            }else{
                return ['code'=>500,'msg'=>'没有此中转箱/任务编码'];
            }
        }else{
            return ['code'=>500,'msg'=>'未获取到中转箱/任务编码'];
        }


        $location = DB::table('cloudhouse_warehouse_location')->where('location_code',$location_code)->where('warehouse_id',$warehouse_id)->select('id')->first();
        if(!empty($location)){
            $location_id = $location->id;
        }else{
            return ['code'=>500,'msg'=>'没有此库位，请核对库位编码是否正确'];
        }

        $location_data = DB::table('cloudhouse_location_num')->where('location_id',$location_id)->select('custom_sku_id','num','lock_num')->get();

        $locationArr = array();
        $locationLock = array();
        if(!$location_data->isEmpty()){
            foreach ($location_data as $d){
                $locationArr[$d->custom_sku_id] = $d->num;
                $locationLock[$d->custom_sku_id] = $d->lock_num;
            }
        }
        $data = json_decode($params['data']);
        db::beginTransaction();
        $box_total = 0;
        foreach ($data as $v){
            $time = date('Y-m-d H:i:s');
            if($v->num<1){
                continue;
            }else{
                $boxDetail = DB::table('goods_transfers_box_detail')->where('box_id',$box_id)->where('custom_sku_id',$v->id);
                if($type==2){
                    $boxDetail = $boxDetail->where('location_ids',$location_id);
                }
                $boxDetail = $boxDetail->select('box_num','shelf_num')->first();
                if(!empty($boxDetail)){
                    $shelf_num = $boxDetail->shelf_num;
                    if($v->num>$boxDetail->box_num){
                        $exceed = $v->num - $boxDetail->box_num;
                        db::rollback();// 回调
                        return ['code'=>500,'msg'=>'操作失败，'.$v->client_sku.'超出任务数量'.$exceed.'件'];
                    }
                }else{
                    db::rollback();// 回调
                    return ['code'=>500,'msg'=>'操作失败，任务中没有'.$v->client_sku];
                }

                $log['spu'] = $v->spu;
                $log['spu_id'] = $this->GetSpuId($v->spu);
                $log['custom_sku_id'] = $v->id;
                $log['custom_sku'] = $v->client_sku;
                $log['num'] = $v->num;
                $log['location_id'] = $location_id;
                $log['time'] = $time;
                $log['type'] = $type;
                $log['order_no'] = $order_no;
                $log['user_id'] = $user_data->Id;
                if(isset($locationArr[$v->id])){
                    if($type==1){
                        $balance = $v->num + $locationArr[$v->id];
                    }elseif ($type==2){
                        $balance = $locationArr[$v->id] - $v->num;
                        $balanceLock =  $locationLock[$v->id] - $v->num;
                        if($balance<0){
                            db::rollback();// 回调
                            return ['code'=>500,'msg'=>'操作失败--'.$v->client_sku.'库位库存不足'];
                        }
                    }
                    $update = DB::table('cloudhouse_location_num')->where('location_id',$location_id)->where('custom_sku_id',$v->id)->update(['num'=>$balance,'lock_num'=>$balanceLock]);
                    if(!$update){
                        db::rollback();// 回调
                        return ['code'=>500,'msg'=>'操作失败--'.$v->client_sku];
                    }

                    $log['now_num'] = $balance;
                    $insertlog = DB::table('cloudhouse_location_log')->insert($log);
                    if(!$insertlog){
                        db::rollback();// 回调
                        return ['code'=>500,'msg'=>'新增日志失败--'.$v->client_sku];
                    }
                }else{
                    if($type==2){
                        db::rollback();// 回调
                        return ['code'=>500,'msg'=>'当前库位没有此产品--'.$v->client_sku];
                    }
                    $insert['spu'] = $v->spu;
                    $insert['spu_id'] = $this->GetSpuId($v->spu);
                    $insert['custom_sku_id'] = $v->id;
                    $insert['custom_sku'] = $v->client_sku;
                    $insert['num'] = $v->num;
                    $insert['location_id'] = $location_id;
                    $insertdata = DB::table('cloudhouse_location_num')->insert($insert);
                    if(!$insertdata){
                        db::rollback();// 回调
                        return ['code'=>500,'msg'=>'操作失败--'.$v->client_sku];
                    }

                    $log['now_num'] = $v->num;
                    $insertlog = DB::table('cloudhouse_location_log')->insert($log);
                    if(!$insertlog){
                        db::rollback();// 回调
                        return ['code'=>500,'msg'=>'新增日志失败--'.$v->client_sku];
                    }
                }

                $update_boxDetail = DB::table('goods_transfers_box_detail')->where('box_id',$box_id)->where('custom_sku_id',$v->id);
                if($type==2){
                    $update_boxDetail = $update_boxDetail->where('location_ids',$location_id);
                }
                $update_boxDetail = $update_boxDetail->update(['shelf_num'=>$v->num+$shelf_num]);
                $box_total += $v->num;
                if(!$update_boxDetail){
                    db::rollback();// 回调
                    return ['code'=>500,'msg'=>'操作失败--'.$v->client_sku];
                }
            }
        }
        DB::table('goods_transfers_box')->where('id',$box_id)->update(['onshelf_num'=>$onshelf_num+$box_total]);
        db::commit();
        return ['code'=>200,'msg'=>'操作成功'];
    }



}

